So there I was...
-
...exploring the msdb.dbo.sysschedules table in Sql Server, and one of the columns is called active_end_time of type int. When I saw the definition, I assumed that it would represent the number of seconds in the day (from midnight). I was wrong (and surprised, bewildered, and downright annoyed) when presented with the reality of the situation. The value returned was 235959 which far exceeds the number of seconds in a day (86400). In reality, it is a numeric representation of "23:59:59". WTF Microsoft!!!! What rocket scientist decided that this was a valid way to represent a time-of-freakin-day?! Even worse, they don't use two-digit hours, so "30000" represents "3 am". Also, "0" indicates midnight, so how do they represent 1 second past midnight? (I don't know because none of existing the job schedules have a time like that.) EDIT ================= Out of curiosity, I added a job with a schedule that starts at 00:00:01, and the value stored in table is "1".
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
...exploring the msdb.dbo.sysschedules table in Sql Server, and one of the columns is called active_end_time of type int. When I saw the definition, I assumed that it would represent the number of seconds in the day (from midnight). I was wrong (and surprised, bewildered, and downright annoyed) when presented with the reality of the situation. The value returned was 235959 which far exceeds the number of seconds in a day (86400). In reality, it is a numeric representation of "23:59:59". WTF Microsoft!!!! What rocket scientist decided that this was a valid way to represent a time-of-freakin-day?! Even worse, they don't use two-digit hours, so "30000" represents "3 am". Also, "0" indicates midnight, so how do they represent 1 second past midnight? (I don't know because none of existing the job schedules have a time like that.) EDIT ================= Out of curiosity, I added a job with a schedule that starts at 00:00:01, and the value stored in table is "1".
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013John Simmons / outlaw programmer wrote:
I assumed that it would represent the number of seconds in the day (from midnight)
I would have, too. It's odd they chose the format they have, but at least it's ordinal and documented[^]. /ravi
My new year resolution: 2048 x 1536 Home | Articles | My .NET bits | Freeware ravib(at)ravib(dot)com
-
...exploring the msdb.dbo.sysschedules table in Sql Server, and one of the columns is called active_end_time of type int. When I saw the definition, I assumed that it would represent the number of seconds in the day (from midnight). I was wrong (and surprised, bewildered, and downright annoyed) when presented with the reality of the situation. The value returned was 235959 which far exceeds the number of seconds in a day (86400). In reality, it is a numeric representation of "23:59:59". WTF Microsoft!!!! What rocket scientist decided that this was a valid way to represent a time-of-freakin-day?! Even worse, they don't use two-digit hours, so "30000" represents "3 am". Also, "0" indicates midnight, so how do they represent 1 second past midnight? (I don't know because none of existing the job schedules have a time like that.) EDIT ================= Out of curiosity, I added a job with a schedule that starts at 00:00:01, and the value stored in table is "1".
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013If 235959 represents 23:59:59 and the type is 'int', how would they have a two digit hour for 3 AM? It seem reasonable that any time represented as a 6 character string without ":" delimiters, when converted to a 'int' would give the appropriate response. So.. a time of 00:00:01, when stripped of delimiters would give 000001 and converted to an 'int' would give... 1. So.. outside of the odd data type, what's the issue?
-
John Simmons / outlaw programmer wrote:
I assumed that it would represent the number of seconds in the day (from midnight)
I would have, too. It's odd they chose the format they have, but at least it's ordinal and documented[^]. /ravi
My new year resolution: 2048 x 1536 Home | Articles | My .NET bits | Freeware ravib(at)ravib(dot)com
I created an extension method to parse it...
public static class ExtendTimeSpan
{
public static TimeSpan ParseFromFormattedInt(int value)
{
return new TimeSpan(0).ParseFromFormattedInt(value);
}public static TimeSpan ParseFromFormattedInt(this TimeSpan span, int value) { TimeSpan result = new TimeSpan(0); string timeString = value.ToString(); string padding = new String('0', 6 - timeString.Length); timeString = ((timeString.Length < 6) ? string.Concat(padding, timeString) : timeString).Insert(4, ":").Insert(2,":"); try { result = TimeSpan.ParseExact(timeString, "g", CultureInfo.CurrentCulture); } catch (Exception) { } return result; }}
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
If 235959 represents 23:59:59 and the type is 'int', how would they have a two digit hour for 3 AM? It seem reasonable that any time represented as a 6 character string without ":" delimiters, when converted to a 'int' would give the appropriate response. So.. a time of 00:00:01, when stripped of delimiters would give 000001 and converted to an 'int' would give... 1. So.. outside of the odd data type, what's the issue?
The issue is that it's an absurd way to represent the time. They're using an int data type, so the number of seconds past midnight would have made more sense, because it requires the same amount of storage space to store 1 as it does to store 1000. This means that in order to determine "the time", I had to write a method to parse the value instead of using
TimeSpan span = TimeSpan.FromSeconds(1);
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
I created an extension method to parse it...
public static class ExtendTimeSpan
{
public static TimeSpan ParseFromFormattedInt(int value)
{
return new TimeSpan(0).ParseFromFormattedInt(value);
}public static TimeSpan ParseFromFormattedInt(this TimeSpan span, int value) { TimeSpan result = new TimeSpan(0); string timeString = value.ToString(); string padding = new String('0', 6 - timeString.Length); timeString = ((timeString.Length < 6) ? string.Concat(padding, timeString) : timeString).Insert(4, ":").Insert(2,":"); try { result = TimeSpan.ParseExact(timeString, "g", CultureInfo.CurrentCulture); } catch (Exception) { } return result; }}
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013:thumbsup: /ravi
My new year resolution: 2048 x 1536 Home | Articles | My .NET bits | Freeware ravib(at)ravib(dot)com
-
...exploring the msdb.dbo.sysschedules table in Sql Server, and one of the columns is called active_end_time of type int. When I saw the definition, I assumed that it would represent the number of seconds in the day (from midnight). I was wrong (and surprised, bewildered, and downright annoyed) when presented with the reality of the situation. The value returned was 235959 which far exceeds the number of seconds in a day (86400). In reality, it is a numeric representation of "23:59:59". WTF Microsoft!!!! What rocket scientist decided that this was a valid way to represent a time-of-freakin-day?! Even worse, they don't use two-digit hours, so "30000" represents "3 am". Also, "0" indicates midnight, so how do they represent 1 second past midnight? (I don't know because none of existing the job schedules have a time like that.) EDIT ================= Out of curiosity, I added a job with a schedule that starts at 00:00:01, and the value stored in table is "1".
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013It's silly, but I see the logic: it's human readable, as well as easily calculated for matches. "Number of seconds since midnight" is a better solution except for the human readable bit: when is 52642? Can you tell exactly and easily without a calculator? Or is it easier to just look at 143722 and know immediately when it is? The silliness if you think about it is having 60 seconds to the minute, 60 minutes to the hour, 24 hours to the day - instead of using a minute that was about 50% longer than the current one, made up of 100 (slightly longer) seconds, and having 1000 new-minutes in a day. We could call it "Stardate" and annoy rabid Trekkies.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!
-
It's silly, but I see the logic: it's human readable, as well as easily calculated for matches. "Number of seconds since midnight" is a better solution except for the human readable bit: when is 52642? Can you tell exactly and easily without a calculator? Or is it easier to just look at 143722 and know immediately when it is? The silliness if you think about it is having 60 seconds to the minute, 60 minutes to the hour, 24 hours to the day - instead of using a minute that was about 50% longer than the current one, made up of 100 (slightly longer) seconds, and having 1000 new-minutes in a day. We could call it "Stardate" and annoy rabid Trekkies.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!
-
It's silly, but I see the logic: it's human readable, as well as easily calculated for matches. "Number of seconds since midnight" is a better solution except for the human readable bit: when is 52642? Can you tell exactly and easily without a calculator? Or is it easier to just look at 143722 and know immediately when it is? The silliness if you think about it is having 60 seconds to the minute, 60 minutes to the hour, 24 hours to the day - instead of using a minute that was about 50% longer than the current one, made up of 100 (slightly longer) seconds, and having 1000 new-minutes in a day. We could call it "Stardate" and annoy rabid Trekkies.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!
OriginalGriff wrote:
"Number of seconds since midnight" is a better solution except for the human readable bit:
It's a numeric value in a database - it doesn't have to be human readable as the actual time. It requires parsing to be used, especially if you want to perform math with it (the SQL required to parse this would be a mild nightmare), and almost always when you simply want to display it. I stand by my original claim that it's absurd.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
...exploring the msdb.dbo.sysschedules table in Sql Server, and one of the columns is called active_end_time of type int. When I saw the definition, I assumed that it would represent the number of seconds in the day (from midnight). I was wrong (and surprised, bewildered, and downright annoyed) when presented with the reality of the situation. The value returned was 235959 which far exceeds the number of seconds in a day (86400). In reality, it is a numeric representation of "23:59:59". WTF Microsoft!!!! What rocket scientist decided that this was a valid way to represent a time-of-freakin-day?! Even worse, they don't use two-digit hours, so "30000" represents "3 am". Also, "0" indicates midnight, so how do they represent 1 second past midnight? (I don't know because none of existing the job schedules have a time like that.) EDIT ================= Out of curiosity, I added a job with a schedule that starts at 00:00:01, and the value stored in table is "1".
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
I created an extension method to parse it...
public static class ExtendTimeSpan
{
public static TimeSpan ParseFromFormattedInt(int value)
{
return new TimeSpan(0).ParseFromFormattedInt(value);
}public static TimeSpan ParseFromFormattedInt(this TimeSpan span, int value) { TimeSpan result = new TimeSpan(0); string timeString = value.ToString(); string padding = new String('0', 6 - timeString.Length); timeString = ((timeString.Length < 6) ? string.Concat(padding, timeString) : timeString).Insert(4, ":").Insert(2,":"); try { result = TimeSpan.ParseExact(timeString, "g", CultureInfo.CurrentCulture); } catch (Exception) { } return result; }}
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013No need to mess around with strings:
public static class ExtendTimeSpan
{
public static TimeSpan ParseFromFormattedInt(int value)
{
int hours = Math.DivRem(value, 10000, out value);
int minutes = Math.DivRem(value, 100, out value);
int seconds = value + minutes * 60 + hours * 3600;
return TimeSpan.FromSeconds(seconds);
}public static TimeSpan ParseFromFormattedInt(this TimeSpan span, int value) { return ParseFromFormattedInt(value); }}
Or, in SQL (2012 or later):
SELECT
TIMEFROMPARTS(active_end_time / 10000, (active_end_time / 100) % 100, active_end_time % 100, 0, 0)
FROM
msdb.dbo.sysschedules
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
It's silly, but I see the logic: it's human readable, as well as easily calculated for matches. "Number of seconds since midnight" is a better solution except for the human readable bit: when is 52642? Can you tell exactly and easily without a calculator? Or is it easier to just look at 143722 and know immediately when it is? The silliness if you think about it is having 60 seconds to the minute, 60 minutes to the hour, 24 hours to the day - instead of using a minute that was about 50% longer than the current one, made up of 100 (slightly longer) seconds, and having 1000 new-minutes in a day. We could call it "Stardate" and annoy rabid Trekkies.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!
Reminds me of an old SNL sketch about new metric time. 100 (current duration) seconds in a metric minute. 100 metric minutes in a metric hour. 100 metric hours in a metric day.
Quote:
The Metric Leisure Week will be composed of three days. Yes, only three days. Monday, Tuesday and Wednesday will become one day known as Mwensday or in decabet: Mwen. Thursday, Friday and Saturday will become Saturthurs, or in decabet: Turth. And Sunday, our traditional day of rest, will remain Sunday. Three days: Mwen, Thurth and Sunday.
-
Reminds me of an old SNL sketch about new metric time. 100 (current duration) seconds in a metric minute. 100 metric minutes in a metric hour. 100 metric hours in a metric day.
Quote:
The Metric Leisure Week will be composed of three days. Yes, only three days. Monday, Tuesday and Wednesday will become one day known as Mwensday or in decabet: Mwen. Thursday, Friday and Saturday will become Saturthurs, or in decabet: Turth. And Sunday, our traditional day of rest, will remain Sunday. Three days: Mwen, Thurth and Sunday.
Yes, it's a joke, but the French actually did it. Decimal time - Wikipedia[^] French Republican Calendar - Wikipedia[^]
Wrong is evil and must be defeated. - Jeff Ello
-
OriginalGriff wrote:
"Number of seconds since midnight" is a better solution except for the human readable bit:
It's a numeric value in a database - it doesn't have to be human readable as the actual time. It requires parsing to be used, especially if you want to perform math with it (the SQL required to parse this would be a mild nightmare), and almost always when you simply want to display it. I stand by my original claim that it's absurd.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013Strikes me that this might be a hangover from the days when memory/disk was in short supply and storing an INT took 16 bits, compared with a 6 char string which took 48. If not directly so, then because the programmer who wrote the code had a history of working on such systems... (First release of SQL Server was 16bit, for OS/2 in 1989 I believe) There was a time when memory was severely constrained, not to mention thousands and thousands of times more expensive than it is now, and likewise processor/disk speed was much slower, so this kind of approach was needed. It's only recently that we have got used to a web page running in a browser using more computing power than an entire organisation once possessed to simply display an annoying pop-up ad with sound and video. 8)
-
...exploring the msdb.dbo.sysschedules table in Sql Server, and one of the columns is called active_end_time of type int. When I saw the definition, I assumed that it would represent the number of seconds in the day (from midnight). I was wrong (and surprised, bewildered, and downright annoyed) when presented with the reality of the situation. The value returned was 235959 which far exceeds the number of seconds in a day (86400). In reality, it is a numeric representation of "23:59:59". WTF Microsoft!!!! What rocket scientist decided that this was a valid way to represent a time-of-freakin-day?! Even worse, they don't use two-digit hours, so "30000" represents "3 am". Also, "0" indicates midnight, so how do they represent 1 second past midnight? (I don't know because none of existing the job schedules have a time like that.) EDIT ================= Out of curiosity, I added a job with a schedule that starts at 00:00:01, and the value stored in table is "1".
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
Strikes me that this might be a hangover from the days when memory/disk was in short supply and storing an INT took 16 bits, compared with a 6 char string which took 48. If not directly so, then because the programmer who wrote the code had a history of working on such systems... (First release of SQL Server was 16bit, for OS/2 in 1989 I believe) There was a time when memory was severely constrained, not to mention thousands and thousands of times more expensive than it is now, and likewise processor/disk speed was much slower, so this kind of approach was needed. It's only recently that we have got used to a web page running in a browser using more computing power than an entire organisation once possessed to simply display an annoying pop-up ad with sound and video. 8)
-
...exploring the msdb.dbo.sysschedules table in Sql Server, and one of the columns is called active_end_time of type int. When I saw the definition, I assumed that it would represent the number of seconds in the day (from midnight). I was wrong (and surprised, bewildered, and downright annoyed) when presented with the reality of the situation. The value returned was 235959 which far exceeds the number of seconds in a day (86400). In reality, it is a numeric representation of "23:59:59". WTF Microsoft!!!! What rocket scientist decided that this was a valid way to represent a time-of-freakin-day?! Even worse, they don't use two-digit hours, so "30000" represents "3 am". Also, "0" indicates midnight, so how do they represent 1 second past midnight? (I don't know because none of existing the job schedules have a time like that.) EDIT ================= Out of curiosity, I added a job with a schedule that starts at 00:00:01, and the value stored in table is "1".
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013If you hadn't mentioned SQL (obligatory Geek & Poke: Simply exåøaomed: SQL[^] I would have understood your worries. Historically, databases (and Cobol :-)) primary belongs in the domain of administration, management and economy. You work with dollars and cents, maybe tenth of a cent as well, but that is exact. If you split a bill of $3.30 on three persons, $1.10 on each, and collect the three $3.30 for giving it to the taxi driver, he receives exactly $3.30. If you do it in the engineering style floating point numbers, the amount that the taxi driver rececives makes you associate to Pentium FDIV bug - Wikipedia[^]. Accountants are as mad at floating point inexactness as engineers are at the Intel bug! So, at least since around 1960, developers of business software (as well as computer manufacturers making machines for executing that software) have been storing values in Binary-coded decimal[^] (BCD) format: Each binary digit takes up 4 bits. Since this gives 16 possibilities, the excess six are usually defined to represent numeric sign (+/-), currency symbol etc. Note that conversion from ASCII to BCD digits is a simple masking (AND) operation, from BCD to ASCII setting a couple bits (OR). Most machines/libraries can handle BCD both eight bits to the digit (after simple ASCII masking) and four bits to the digit (sometimes called 'packed BCD'), for space saving. Several CPU architectures support arithmetic operations on BCD values - although some not fully: I was working with a machine that had hardware (well, actually it was microcoded) add, subtract, and multiply of BCD. Dynamic analysis of real-life Cobol applications showed that BCD division was almost never done. The cost of implementing BCD divide in hardware was too high to justify it, so the instruction code was defined, causing an "Unimplemented instruction code" interrupt, and an interrupt handler performed the operation in software. (Very slow,
-
But no of secs in a day = 86400 (17 bits if signed int, 16 bits if unsigned int) However, 235959 needs 18 bits if signed int or 17 bits if unsigned int
You are right, of course. I don't know how things were/are stored internally in SQL Server, but even in 16 bit stuff you could usually store 32bit INTs, which is still smaller than the 48bits needed for "235959". You could usually manipulate DWORDS (32 bits) with single machine instructions, where as to manipulate "235959" would require a whole subroutine. Hence my comment. These days no-one thinks twice (or even once) about using a few megabytes of inefficient code to splurge something onto your browser window, but I suspect in the highly competitive world of high-end/large/NoSQL database implementations where speed is often of the essence, such apparently strange optimisations are still being used.
-
You are right, of course. I don't know how things were/are stored internally in SQL Server, but even in 16 bit stuff you could usually store 32bit INTs, which is still smaller than the 48bits needed for "235959". You could usually manipulate DWORDS (32 bits) with single machine instructions, where as to manipulate "235959" would require a whole subroutine. Hence my comment. These days no-one thinks twice (or even once) about using a few megabytes of inefficient code to splurge something onto your browser window, but I suspect in the highly competitive world of high-end/large/NoSQL database implementations where speed is often of the essence, such apparently strange optimisations are still being used.
Mike Winiberg wrote:
You are right, of course. I don't know how things were/are stored internally in SQL Server, but even in 16 bit stuff you could usually store 32bit INTs, which is still smaller than the 48bits needed for "235959".
You would only need 48bits if you were storing as text. 2,147,483,647 can be stored in a signed 32bit INT.
Michael Martin Australia "I controlled my laughter and simple said "No,I am very busy,so I can't write any code for you". The moment they heard this all the smiling face turned into a sad looking face and one of them farted. So I had to leave the place as soon as possible." - Mr.Prakash One Fine Saturday. 24/04/2004
-
It's silly, but I see the logic: it's human readable, as well as easily calculated for matches. "Number of seconds since midnight" is a better solution except for the human readable bit: when is 52642? Can you tell exactly and easily without a calculator? Or is it easier to just look at 143722 and know immediately when it is? The silliness if you think about it is having 60 seconds to the minute, 60 minutes to the hour, 24 hours to the day - instead of using a minute that was about 50% longer than the current one, made up of 100 (slightly longer) seconds, and having 1000 new-minutes in a day. We could call it "Stardate" and annoy rabid Trekkies.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!
OriginalGriff wrote:
The silliness if you think about it is having 60 seconds to the minute, 60 minutes to the hour, 24 hours to the day - instead of using a minute that was about 50% longer than the current one, made up of 100 (slightly longer) seconds, and having 1000 new-minutes in a day.
There is a rationale behind using 24 / 60 / 60. It's the same as the one with 360 degrees instead 100. 60 and 24 (and by extension 3600 is easier to divide than 100. 12 and 24 are easier to divide than 10 or 20, and 60 is easier to divide than 100. You can divide 10 by 1, 2, 5, 10 You can divide 12 by 1, 2, 3, 4, 6, 12 You can divide 20 by 1, 2, 4, 5, 10, 20 You can divide 24 by 1, 2, 3, 4, 6, 8, 12, 24 You can divide 60 by 1, 2, 3, 4, 5, 6, 10, 12, 15, 20, 30, 60 without running into decimal places You can divide 100 by 1, 2, 4, 5, 10, 20, 25, 50, 100 without running into decimal places The most important ones of these is probably to be able to divide by 2, 3 and 4 without having a remainder.
