So there I was...
-
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.
-
No 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
Richard Deeming wrote:
Or, in SQL (2012 or later):
This is 2008R2... :/
".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, 2013I worked on a system where date was stored as (year << 16) | (month << 8) | (day-of-month) When looking at the raw data, we used hex. 07E10A05 (07E1-0A-05) would be today's date: 2017-10-05. Sorted correctly in the indexes and was very easy to interpret for month and day. I am sure that the implementer of the SQL field was trying to make it user friendly for display. I guess it HAS to be interpreted based on the local system/DB time zone.
-
No 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
I knew a math way was possible. This can also be extended to the dates that are stored in the same fashion: Given an int value of 99991231:
int year = Math.DivRem(value, 10000, out value);
int month = Math.DivRem(value, 100, out value);
int day = value;
return new DateTime(year, month, day);would yield a datetime of 12/31/9999.
".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 -
OriginalGriff wrote:
It's silly, but I see the logic
The logic is that you don't invent yet another representation for time values... Who felt this was necessary?
I cannot tell you who thought it was necessary, but it is very useful for report builders. You have a stored procedure running daily and you concatenate the numeric date on the end without needing conversions: MyReportOnFinances_20171005.csv
-
I knew a math way was possible. This can also be extended to the dates that are stored in the same fashion: Given an int value of 99991231:
int year = Math.DivRem(value, 10000, out value);
int month = Math.DivRem(value, 100, out value);
int day = value;
return new DateTime(year, month, day);would yield a datetime of 12/31/9999.
".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, 2013As if integer time wasn't bad enough! :laugh: We do a lot of work with a Norwegian ERP system that stores dates like that. The UI validates the day and month, but doesn't validate the year. We regularly have to correct user input like
200171005or2011130. It's also allergic toNullin the database. If a date hasn't been entered, it's stored as0. Fun times.
"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!
You would also have to rewrite every table of physical constants. If you think the confusion between Imperial and Metric units is bad, just wait until "new metric" is added. OTOH, if the Metro supermarket chain doesn't object, perhaps we could call it that... :D
If you have an important point to make, don't try to be subtle or clever. Use a pile driver. Hit the point once. Then come back and hit it again. Then hit it a third time - a tremendous whack. --Winston Churchill
-
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, 2013I feel your pain. I have spent a considerable amount of time trying to unravel a MySql database that someone else built. Various dates were stored as integers, decimal, string, in fact anything except the obvious DATE! How do you know if a date value of 1216 represents 1st February 2016, 12th January 2006 or what? Grrr!
We're philosophical about power outages here. A.C. come, A.C. go.
