How to convert this time format from SQL Server database
So we have a software vendor that has time stored in a database in a CHAR(216) field. In the GUI you can select open hours for a door in increments of 15 minutes. So in my example below the hours would be open Mon-Fri from 8 AM to 5 PM. So the fun part. Here is how the time bits are stored in SQL for my example below. From what I can tell the data is stored in order from Sunday-Saturday. Does anyone have any ideas on how I would be able to convert this into the actual days and hours?
Mon-Fri 8AM-5PM: 00000000000000000000000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F000000000000000000000000000000000000000000000000000000000000000000000000000000
All Closed:
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
All Open:
FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
sql
sql-server asked Nov 15 '18 at 14:34
AlanAlan
476
add a comment |
So we have a software vendor that has time stored in a database in a CHAR(216) field. In the GUI you can select open hours for a door in increments of 15 minutes. So in my example below the hours would be open Mon-Fri from 8 AM to 5 PM. So the fun part. Here is how the time bits are stored in SQL for my example below. From what I can tell the data is stored in order from Sunday-Saturday. Does anyone have any ideas on how I would be able to convert this into the actual days and hours?
Mon-Fri 8AM-5PM: 00000000000000000000000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F000000000000000000000000000000000000000000000000000000000000000000000000000000
All Closed:
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
All Open:
FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
sql
sql-server asked Nov 15 '18 at 14:34
AlanAlan
476
4
That's not a time format. You need to write a program to parse this string (as in a parser, not justint.Parse)
– Panagiotis Kanavos
Nov 15 '18 at 14:37
1
I was expecting each character to represent a 15 minute interval, but it can't (24*4=96 and 24*4*7=672). What does each individual character represent here?
– Larnu
Nov 15 '18 at 14:38
If I only check the first box for Sunday 00:15 the string goes from all 0's to 01 with all 0s. If I check the first two boxes it goes to 03 with all 0s after.
– Alan
Nov 15 '18 at 14:45
The second character is a1, well that throws whatever I was thinking to the wind? Without knowing the algorithm there's little we can do to help here.
– Larnu
Nov 15 '18 at 14:47
add a comment |
So we have a software vendor that has time stored in a database in a CHAR(216) field. In the GUI you can select open hours for a door in increments of 15 minutes. So in my example below the hours would be open Mon-Fri from 8 AM to 5 PM. So the fun part. Here is how the time bits are stored in SQL for my example below. From what I can tell the data is stored in order from Sunday-Saturday. Does anyone have any ideas on how I would be able to convert this into the actual days and hours?
Mon-Fri 8AM-5PM: 00000000000000000000000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F000000000000000000000000000000000000000000000000000000000000000000000000000000
All Closed:
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
All Open:
FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
sql
sql-server asked Nov 15 '18 at 14:34
AlanAlan
476
So we have a software vendor that has time stored in a database in a CHAR(216) field. In the GUI you can select open hours for a door in increments of 15 minutes. So in my example below the hours would be open Mon-Fri from 8 AM to 5 PM. So the fun part. Here is how the time bits are stored in SQL for my example below. From what I can tell the data is stored in order from Sunday-Saturday. Does anyone have any ideas on how I would be able to convert this into the actual days and hours?
Mon-Fri 8AM-5PM: 00000000000000000000000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F00000000000000FFFFFFFF0F000000000000000000000000000000000000000000000000000000000000000000000000000000
All Closed:
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
All Open:
FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
sql
sql-server sql
sql-server asked Nov 15 '18 at 14:34
AlanAlan
476
asked Nov 15 '18 at 14:34
AlanAlan
476
asked Nov 15 '18 at 14:34
AlanAlan
476
asked Nov 15 '18 at 14:34
AlanAlan
476
asked Nov 15 '18 at 14:34
AlanAlan
476
476
4
That's not a time format. You need to write a program to parse this string (as in a parser, not justint.Parse)
– Panagiotis Kanavos
Nov 15 '18 at 14:37
1
I was expecting each character to represent a 15 minute interval, but it can't (24*4=96 and 24*4*7=672). What does each individual character represent here?
– Larnu
Nov 15 '18 at 14:38
If I only check the first box for Sunday 00:15 the string goes from all 0's to 01 with all 0s. If I check the first two boxes it goes to 03 with all 0s after.
– Alan
Nov 15 '18 at 14:45
The second character is a1, well that throws whatever I was thinking to the wind? Without knowing the algorithm there's little we can do to help here.
– Larnu
Nov 15 '18 at 14:47
add a comment |
4
That's not a time format. You need to write a program to parse this string (as in a parser, not justint.Parse)
– Panagiotis Kanavos
Nov 15 '18 at 14:37
1
I was expecting each character to represent a 15 minute interval, but it can't (24*4=96 and 24*4*7=672). What does each individual character represent here?
– Larnu
Nov 15 '18 at 14:38
If I only check the first box for Sunday 00:15 the string goes from all 0's to 01 with all 0s. If I check the first two boxes it goes to 03 with all 0s after.
– Alan
Nov 15 '18 at 14:45
The second character is a1, well that throws whatever I was thinking to the wind? Without knowing the algorithm there's little we can do to help here.
– Larnu
Nov 15 '18 at 14:47
4
4
That's not a time format. You need to write a program to parse this string (as in a parser, not just
int.Parse)– Panagiotis Kanavos
Nov 15 '18 at 14:37
That's not a time format. You need to write a program to parse this string (as in a parser, not just
int.Parse)– Panagiotis Kanavos
Nov 15 '18 at 14:37
1
1
I was expecting each character to represent a 15 minute interval, but it can't (24*4=96 and 24*4*7=672). What does each individual character represent here?
– Larnu
Nov 15 '18 at 14:38
I was expecting each character to represent a 15 minute interval, but it can't (24*4=96 and 24*4*7=672). What does each individual character represent here?
– Larnu
Nov 15 '18 at 14:38
If I only check the first box for Sunday 00:15 the string goes from all 0's to 01 with all 0s. If I check the first two boxes it goes to 03 with all 0s after.
– Alan
Nov 15 '18 at 14:45
If I only check the first box for Sunday 00:15 the string goes from all 0's to 01 with all 0s. If I check the first two boxes it goes to 03 with all 0s after.
– Alan
Nov 15 '18 at 14:45
The second character is a
1, well that throws whatever I was thinking to the wind? Without knowing the algorithm there's little we can do to help here.– Larnu
Nov 15 '18 at 14:47
The second character is a
1, well that throws whatever I was thinking to the wind? Without knowing the algorithm there's little we can do to help here.– Larnu
Nov 15 '18 at 14:47
add a comment |
1 Answer
1
active
oldest
votes
Calculate the index i of every 1 bit in the stream. i*15 is the number of minutes since the start of the week (Sunday, 00:00?), so you add that number of minutes to the start of the week and get the point in time where that opening interval begins.
Apparently, the data is represented byte-wise with the LSB first. Note that each hex character represents 4 bits, i.e. exactly one hour.
You could iterate over the string, extracting two hex characters at a time, converting to an integer and then finding the bits set while incrementing your index i for every bit processed.
Pseudo code:
byte currentByte;
for ( int bitIndex = 0; bitIndex < (216*4); bitIndex++ ) {
if ( bitIndex % 8 == 0 ) {
/* Need to fetch next byte from the bit-map */
int stringIndex = bitIndex / 4; /* 4 bits make up one character */
String hexByte = substring( input, stringIndex, stringIndex + 2); /* get next two hex characters */
currentByte = parse_hex( hexByte );
}
if ( (currentByte & 1) != 0 ) {
/* Lowest bit is set -> open! */
output( "Open starting at " + (bitIndex * 15) + " minutes from the start of the week.");
} else {
/* Lowest bit not set -> closed! */
}
currentByte = currentByte / 2; /* logical shift right by 1 bit */
}
answered Nov 15 '18 at 14:47
JimmyBJimmyB
9,54811838
Thanks, I'll run with this and see what I can do. I'll never understand why developers seem to make things harder than they need to be.
– Alan
Nov 15 '18 at 14:55
1
@Alan: you may well find this arcane format is fed (or was originally fed) directly to the computing equivalent of a toaster, a grain of embedded transistors that's actually hooked up to the door actuators. In this case "just go with what the thing processes so we don't have to invent another format" is the path of least resistance, as opposed to "let's store things in a proper normalized way". Not an excuse, of course.
– Jeroen Mostert
Nov 15 '18 at 15:06
I'm not sure we could come up with anything significantly more simple to represent 672 binary values in a database. One could use only1and0in the string perhaps (4x as much space used), or use a binary data type (50% less space used, but possibly not as well supported on different DBMS), or split the bits up into X values of N bits each (32 (integer)? 64 (long integer)?),... Or do a proper many-to-many relational structure. But would that really be more simple?
– JimmyB
Nov 15 '18 at 15:08
@JimmyB: unless the application is managing tens of thousands of doors, I don't think the space consideration is really that big a deal... and in that vein, I'd prefer a table with 672 rows, one for every interval, complete withFromandTo. (Actually, I'd prefer one where the number of rows are variable, but that would make the GUI more complicated, as it would have to actually consolidate intervals, which may be too much to ask for... Although I imagine most end users would actually also be happier with a way to specify intervals that don't require a ton of clicks.)
– Jeroen Mostert
Nov 15 '18 at 15:17
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321754%2fhow-to-convert-this-time-format-from-sql-server-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Calculate the index i of every 1 bit in the stream. i*15 is the number of minutes since the start of the week (Sunday, 00:00?), so you add that number of minutes to the start of the week and get the point in time where that opening interval begins.
Apparently, the data is represented byte-wise with the LSB first. Note that each hex character represents 4 bits, i.e. exactly one hour.
You could iterate over the string, extracting two hex characters at a time, converting to an integer and then finding the bits set while incrementing your index i for every bit processed.
Pseudo code:
byte currentByte;
for ( int bitIndex = 0; bitIndex < (216*4); bitIndex++ ) {
if ( bitIndex % 8 == 0 ) {
/* Need to fetch next byte from the bit-map */
int stringIndex = bitIndex / 4; /* 4 bits make up one character */
String hexByte = substring( input, stringIndex, stringIndex + 2); /* get next two hex characters */
currentByte = parse_hex( hexByte );
}
if ( (currentByte & 1) != 0 ) {
/* Lowest bit is set -> open! */
output( "Open starting at " + (bitIndex * 15) + " minutes from the start of the week.");
} else {
/* Lowest bit not set -> closed! */
}
currentByte = currentByte / 2; /* logical shift right by 1 bit */
}
answered Nov 15 '18 at 14:47
JimmyBJimmyB
9,54811838
Thanks, I'll run with this and see what I can do. I'll never understand why developers seem to make things harder than they need to be.
– Alan
Nov 15 '18 at 14:55
1
@Alan: you may well find this arcane format is fed (or was originally fed) directly to the computing equivalent of a toaster, a grain of embedded transistors that's actually hooked up to the door actuators. In this case "just go with what the thing processes so we don't have to invent another format" is the path of least resistance, as opposed to "let's store things in a proper normalized way". Not an excuse, of course.
– Jeroen Mostert
Nov 15 '18 at 15:06
I'm not sure we could come up with anything significantly more simple to represent 672 binary values in a database. One could use only1and0in the string perhaps (4x as much space used), or use a binary data type (50% less space used, but possibly not as well supported on different DBMS), or split the bits up into X values of N bits each (32 (integer)? 64 (long integer)?),... Or do a proper many-to-many relational structure. But would that really be more simple?
– JimmyB
Nov 15 '18 at 15:08
@JimmyB: unless the application is managing tens of thousands of doors, I don't think the space consideration is really that big a deal... and in that vein, I'd prefer a table with 672 rows, one for every interval, complete withFromandTo. (Actually, I'd prefer one where the number of rows are variable, but that would make the GUI more complicated, as it would have to actually consolidate intervals, which may be too much to ask for... Although I imagine most end users would actually also be happier with a way to specify intervals that don't require a ton of clicks.)
– Jeroen Mostert
Nov 15 '18 at 15:17
add a comment |
Calculate the index i of every 1 bit in the stream. i*15 is the number of minutes since the start of the week (Sunday, 00:00?), so you add that number of minutes to the start of the week and get the point in time where that opening interval begins.
Apparently, the data is represented byte-wise with the LSB first. Note that each hex character represents 4 bits, i.e. exactly one hour.
You could iterate over the string, extracting two hex characters at a time, converting to an integer and then finding the bits set while incrementing your index i for every bit processed.
Pseudo code:
byte currentByte;
for ( int bitIndex = 0; bitIndex < (216*4); bitIndex++ ) {
if ( bitIndex % 8 == 0 ) {
/* Need to fetch next byte from the bit-map */
int stringIndex = bitIndex / 4; /* 4 bits make up one character */
String hexByte = substring( input, stringIndex, stringIndex + 2); /* get next two hex characters */
currentByte = parse_hex( hexByte );
}
if ( (currentByte & 1) != 0 ) {
/* Lowest bit is set -> open! */
output( "Open starting at " + (bitIndex * 15) + " minutes from the start of the week.");
} else {
/* Lowest bit not set -> closed! */
}
currentByte = currentByte / 2; /* logical shift right by 1 bit */
}
answered Nov 15 '18 at 14:47
JimmyBJimmyB
9,54811838
Thanks, I'll run with this and see what I can do. I'll never understand why developers seem to make things harder than they need to be.
– Alan
Nov 15 '18 at 14:55
1
@Alan: you may well find this arcane format is fed (or was originally fed) directly to the computing equivalent of a toaster, a grain of embedded transistors that's actually hooked up to the door actuators. In this case "just go with what the thing processes so we don't have to invent another format" is the path of least resistance, as opposed to "let's store things in a proper normalized way". Not an excuse, of course.
– Jeroen Mostert
Nov 15 '18 at 15:06
I'm not sure we could come up with anything significantly more simple to represent 672 binary values in a database. One could use only1and0in the string perhaps (4x as much space used), or use a binary data type (50% less space used, but possibly not as well supported on different DBMS), or split the bits up into X values of N bits each (32 (integer)? 64 (long integer)?),... Or do a proper many-to-many relational structure. But would that really be more simple?
– JimmyB
Nov 15 '18 at 15:08
@JimmyB: unless the application is managing tens of thousands of doors, I don't think the space consideration is really that big a deal... and in that vein, I'd prefer a table with 672 rows, one for every interval, complete withFromandTo. (Actually, I'd prefer one where the number of rows are variable, but that would make the GUI more complicated, as it would have to actually consolidate intervals, which may be too much to ask for... Although I imagine most end users would actually also be happier with a way to specify intervals that don't require a ton of clicks.)
– Jeroen Mostert
Nov 15 '18 at 15:17
add a comment |
Calculate the index i of every 1 bit in the stream. i*15 is the number of minutes since the start of the week (Sunday, 00:00?), so you add that number of minutes to the start of the week and get the point in time where that opening interval begins.
Apparently, the data is represented byte-wise with the LSB first. Note that each hex character represents 4 bits, i.e. exactly one hour.
You could iterate over the string, extracting two hex characters at a time, converting to an integer and then finding the bits set while incrementing your index i for every bit processed.
Pseudo code:
byte currentByte;
for ( int bitIndex = 0; bitIndex < (216*4); bitIndex++ ) {
if ( bitIndex % 8 == 0 ) {
/* Need to fetch next byte from the bit-map */
int stringIndex = bitIndex / 4; /* 4 bits make up one character */
String hexByte = substring( input, stringIndex, stringIndex + 2); /* get next two hex characters */
currentByte = parse_hex( hexByte );
}
if ( (currentByte & 1) != 0 ) {
/* Lowest bit is set -> open! */
output( "Open starting at " + (bitIndex * 15) + " minutes from the start of the week.");
} else {
/* Lowest bit not set -> closed! */
}
currentByte = currentByte / 2; /* logical shift right by 1 bit */
}
answered Nov 15 '18 at 14:47
JimmyBJimmyB
9,54811838
Calculate the index i of every 1 bit in the stream. i*15 is the number of minutes since the start of the week (Sunday, 00:00?), so you add that number of minutes to the start of the week and get the point in time where that opening interval begins.
Apparently, the data is represented byte-wise with the LSB first. Note that each hex character represents 4 bits, i.e. exactly one hour.
You could iterate over the string, extracting two hex characters at a time, converting to an integer and then finding the bits set while incrementing your index i for every bit processed.
Pseudo code:
byte currentByte;
for ( int bitIndex = 0; bitIndex < (216*4); bitIndex++ ) {
if ( bitIndex % 8 == 0 ) {
/* Need to fetch next byte from the bit-map */
int stringIndex = bitIndex / 4; /* 4 bits make up one character */
String hexByte = substring( input, stringIndex, stringIndex + 2); /* get next two hex characters */
currentByte = parse_hex( hexByte );
}
if ( (currentByte & 1) != 0 ) {
/* Lowest bit is set -> open! */
output( "Open starting at " + (bitIndex * 15) + " minutes from the start of the week.");
} else {
/* Lowest bit not set -> closed! */
}
currentByte = currentByte / 2; /* logical shift right by 1 bit */
}
answered Nov 15 '18 at 14:47
JimmyBJimmyB
9,54811838
edited Nov 15 '18 at 15:11
answered Nov 15 '18 at 14:47
JimmyBJimmyB
9,54811838
answered Nov 15 '18 at 14:47
JimmyBJimmyB
9,54811838
answered Nov 15 '18 at 14:47
JimmyBJimmyB
9,54811838
9,54811838
Thanks, I'll run with this and see what I can do. I'll never understand why developers seem to make things harder than they need to be.
– Alan
Nov 15 '18 at 14:55
1
@Alan: you may well find this arcane format is fed (or was originally fed) directly to the computing equivalent of a toaster, a grain of embedded transistors that's actually hooked up to the door actuators. In this case "just go with what the thing processes so we don't have to invent another format" is the path of least resistance, as opposed to "let's store things in a proper normalized way". Not an excuse, of course.
– Jeroen Mostert
Nov 15 '18 at 15:06
I'm not sure we could come up with anything significantly more simple to represent 672 binary values in a database. One could use only1and0in the string perhaps (4x as much space used), or use a binary data type (50% less space used, but possibly not as well supported on different DBMS), or split the bits up into X values of N bits each (32 (integer)? 64 (long integer)?),... Or do a proper many-to-many relational structure. But would that really be more simple?
– JimmyB
Nov 15 '18 at 15:08
@JimmyB: unless the application is managing tens of thousands of doors, I don't think the space consideration is really that big a deal... and in that vein, I'd prefer a table with 672 rows, one for every interval, complete withFromandTo. (Actually, I'd prefer one where the number of rows are variable, but that would make the GUI more complicated, as it would have to actually consolidate intervals, which may be too much to ask for... Although I imagine most end users would actually also be happier with a way to specify intervals that don't require a ton of clicks.)
– Jeroen Mostert
Nov 15 '18 at 15:17
add a comment |
Thanks, I'll run with this and see what I can do. I'll never understand why developers seem to make things harder than they need to be.
– Alan
Nov 15 '18 at 14:55
1
@Alan: you may well find this arcane format is fed (or was originally fed) directly to the computing equivalent of a toaster, a grain of embedded transistors that's actually hooked up to the door actuators. In this case "just go with what the thing processes so we don't have to invent another format" is the path of least resistance, as opposed to "let's store things in a proper normalized way". Not an excuse, of course.
– Jeroen Mostert
Nov 15 '18 at 15:06
I'm not sure we could come up with anything significantly more simple to represent 672 binary values in a database. One could use only1and0in the string perhaps (4x as much space used), or use a binary data type (50% less space used, but possibly not as well supported on different DBMS), or split the bits up into X values of N bits each (32 (integer)? 64 (long integer)?),... Or do a proper many-to-many relational structure. But would that really be more simple?
– JimmyB
Nov 15 '18 at 15:08
@JimmyB: unless the application is managing tens of thousands of doors, I don't think the space consideration is really that big a deal... and in that vein, I'd prefer a table with 672 rows, one for every interval, complete withFromandTo. (Actually, I'd prefer one where the number of rows are variable, but that would make the GUI more complicated, as it would have to actually consolidate intervals, which may be too much to ask for... Although I imagine most end users would actually also be happier with a way to specify intervals that don't require a ton of clicks.)
– Jeroen Mostert
Nov 15 '18 at 15:17
Thanks, I'll run with this and see what I can do. I'll never understand why developers seem to make things harder than they need to be.
– Alan
Nov 15 '18 at 14:55
Thanks, I'll run with this and see what I can do. I'll never understand why developers seem to make things harder than they need to be.
– Alan
Nov 15 '18 at 14:55
1
1
@Alan: you may well find this arcane format is fed (or was originally fed) directly to the computing equivalent of a toaster, a grain of embedded transistors that's actually hooked up to the door actuators. In this case "just go with what the thing processes so we don't have to invent another format" is the path of least resistance, as opposed to "let's store things in a proper normalized way". Not an excuse, of course.
– Jeroen Mostert
Nov 15 '18 at 15:06
@Alan: you may well find this arcane format is fed (or was originally fed) directly to the computing equivalent of a toaster, a grain of embedded transistors that's actually hooked up to the door actuators. In this case "just go with what the thing processes so we don't have to invent another format" is the path of least resistance, as opposed to "let's store things in a proper normalized way". Not an excuse, of course.
– Jeroen Mostert
Nov 15 '18 at 15:06
I'm not sure we could come up with anything significantly more simple to represent 672 binary values in a database. One could use only
1 and 0 in the string perhaps (4x as much space used), or use a binary data type (50% less space used, but possibly not as well supported on different DBMS), or split the bits up into X values of N bits each (32 (integer)? 64 (long integer)?),... Or do a proper many-to-many relational structure. But would that really be more simple?– JimmyB
Nov 15 '18 at 15:08
I'm not sure we could come up with anything significantly more simple to represent 672 binary values in a database. One could use only
1 and 0 in the string perhaps (4x as much space used), or use a binary data type (50% less space used, but possibly not as well supported on different DBMS), or split the bits up into X values of N bits each (32 (integer)? 64 (long integer)?),... Or do a proper many-to-many relational structure. But would that really be more simple?– JimmyB
Nov 15 '18 at 15:08
@JimmyB: unless the application is managing tens of thousands of doors, I don't think the space consideration is really that big a deal... and in that vein, I'd prefer a table with 672 rows, one for every interval, complete with
From and To. (Actually, I'd prefer one where the number of rows are variable, but that would make the GUI more complicated, as it would have to actually consolidate intervals, which may be too much to ask for... Although I imagine most end users would actually also be happier with a way to specify intervals that don't require a ton of clicks.)– Jeroen Mostert
Nov 15 '18 at 15:17
@JimmyB: unless the application is managing tens of thousands of doors, I don't think the space consideration is really that big a deal... and in that vein, I'd prefer a table with 672 rows, one for every interval, complete with
From and To. (Actually, I'd prefer one where the number of rows are variable, but that would make the GUI more complicated, as it would have to actually consolidate intervals, which may be too much to ask for... Although I imagine most end users would actually also be happier with a way to specify intervals that don't require a ton of clicks.)– Jeroen Mostert
Nov 15 '18 at 15:17
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321754%2fhow-to-convert-this-time-format-from-sql-server-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
4
That's not a time format. You need to write a program to parse this string (as in a parser, not just
int.Parse)– Panagiotis Kanavos
Nov 15 '18 at 14:37
1
I was expecting each character to represent a 15 minute interval, but it can't (24*4=96 and 24*4*7=672). What does each individual character represent here?
– Larnu
Nov 15 '18 at 14:38
If I only check the first box for Sunday 00:15 the string goes from all 0's to 01 with all 0s. If I check the first two boxes it goes to 03 with all 0s after.
– Alan
Nov 15 '18 at 14:45
The second character is a
1, well that throws whatever I was thinking to the wind? Without knowing the algorithm there's little we can do to help here.– Larnu
Nov 15 '18 at 14:47