Date/Time floats



  • The help desk application that we use where I work has a very strange way of storing date/time fields.  I've seen various ways to do this - actual date/time fields, some variant of the epoch (like the number of seconds since the lead developers birth day), or even as a character string that you have to parse. Between the definition:

     

    [SCH_START_TIME] [float] NULL

    And the actual way they decided to store values, like this:

     

    20110124160500

    Which stands for January 24th, 2011, at 4:05 PM, it has taken it to a whole new level.  Remember, it's a float, not a character string!



  • They probably thought "it's a number, so we'll store it in the database as one", then used FLOAT because their integer data type was only 32-bit.



  • @Quietust said:

    They probably thought "it's a number, so we'll store it in the database as one", then used FLOAT because their integer data type was only 32-bit.

    As long as the magnitude of the number does not exceed the number of bits in the mantissa, I dont see any problem with it either..



  •  Filed under "dear god why?"

     I think that code is paying homage to a different deity. Chtulhu or Nyarlathotep perhaps.



  • @havokk said:

     Filed under "dear god why?"

     I think that code is paying homage to a different deity. Chtulhu or Nyarlathotep perhaps.

     If 64 bit integers are supported, then I agree. But if you only have 32 bits for an integer, this is a decent way to work with 52  bits as an integral quantity with all of the normal math operators, etc...



  • @TheCPUWizard said:

    @havokk said:

     I think that code is paying homage to a different deity. Chtulhu or Nyarlathotep perhaps.

     If 64 bit integers are supported, then I agree. But if you only have 32 bits for an integer, this is a decent way to work with 52  bits as an integral quantity with all of the normal math operators, etc...

     

    Yes, "normal math operators" would be great.  Like, subtracting a day.

    I think the easiest way we found to work with these is to cast them as a string, pull the individual pieces out and put them back together using "real" date functions.  I certainly didn't want to duplicate some of the other, rather arcane and convoluted "home grown" date handling functions I've read on here... 

    Isn't your comment sort of in the same spirit as decimal packing, at least from the view of making every bit count?

     



  • @mahlerrd said:

    @TheCPUWizard said:

    @havokk said:

     I think that code is paying homage to a different deity. Chtulhu or Nyarlathotep perhaps.

     If 64 bit integers are supported, then I agree. But if you only have 32 bits for an integer, this is a decent way to work with 52  bits as an integral quantity with all of the normal math operators, etc...

     

    Yes, "normal math operators" would be great.  Like, subtracting a day.

    I think the easiest way we found to work with these is to cast them as a string, pull the individual pieces out and put them back together using "real" date functions.  I certainly didn't want to duplicate some of the other, rather arcane and convoluted "home grown" date handling functions I've read on here... 

    Isn't your comment sort of in the same spirit as decimal packing, at least from the view of making every bit count?

    Very much in th same spirit [with all of the same issues, both pro and con]

    But why involve string? [faking the number of zeros because it is late and I am lazy

    int dayofmonth = (uglynumber / 10000) % 100
    int year = (uglynumber /10000000)
    int month = (uglynumber / 100000) % 100

    Sure to be faster (and less memory)  than the strings. I usually have a helper class so

    int dayofmonth = GetDigits(ugly, 3,2)



  • @TheCPUWizard said:

    But why involve string? [faking the number of zeros because it is late and I am lazy

    int dayofmonth = (uglynumber / 10000) % 100
    int year = (uglynumber /10000000)
    int month = (uglynumber / 100000) % 100

    Sure to be faster (and less memory)  than the strings. I usually have a helper class so

    int dayofmonth = GetDigits(ugly, 3,2)


    Yeah, because this has not been a solved-and-booooring problem for decades and there is no convenient data type to store dates and times, or even datetimes, so you absolutely, positively, need to reinvent the (occasionally exploding) square wheel. Congratulations, you are TRWTF.



  • No clue on why the float, but the logic for storing dates like that goes like this:
    Using date-time compares is so 2008. We need to be able to ASCII-sort datetimes. It's so much faster, and saves us from having to figure out how to use date-time compare functions!



  • @piskvorr said:

    @TheCPUWizard said:
    But why involve string? [faking the number of zeros because it is late and I am lazy

     

    int dayofmonth = (uglynumber / 10000) % 100
    int year = (uglynumber /10000000)
    int month = (uglynumber / 100000) % 100

    Sure to be faster (and less memory)  than the strings. I usually have a helper class so

    int dayofmonth = GetDigits(ugly, 3,2)

    Yeah, because this has not been a solved-and-booooring problem for *decades* and there is no convenient data type to store dates and times, or even datetimes, so you absolutely, positively, *need* to reinvent the (occasionally exploding) square wheel. Congratulations, you are TRWTF.
    Whoosh


  • @piskvorr said:

    @TheCPUWizard said:
    But why involve string? [faking the number of zeros because it is late and I am lazy

    int dayofmonth = (uglynumber / 10000) % 100
    int year = (uglynumber /10000000)
    int month = (uglynumber / 100000) % 100

    Sure to be faster (and less memory)  than the strings. I usually have a helper class so

    int dayofmonth = GetDigits(ugly, 3,2)


    Yeah, because this has not been a solved-and-booooring problem for decades and there is no convenient data type to store dates and times, or even datetimes, so you absolutely, positively, need to reinvent the (occasionally exploding) square wheel. Congratulations, you are TRWTF.

    There's actually a sensible way of storing dates this way, the Julian Day Count. I use it in my library to find the Mayan date, since it's by far the best way of counting dates before the Julian->Gregorian conversion. (Of course this problem will be substantially easier past December, 2012, since the Long Count has a new epoch...)

    The difference, of course, is that the Julian Day Count is:
    1) An accepted standard
    2) Well-documented
    3) Developed by historians and archeologists in support of scientific projects
    4) Backed by decades of productive use in solving problems

    This crap is none of those things.



  • @tweek said:

    No clue on why the float, but the logic for storing dates like that goes like this:
    Using date-time compares is so 2008. We need to be able to ASCII-sort datetimes. It's so much faster, and saves us from having to figure out how to use date-time compare functions!

    ISO 8601.  It's clearly a string, because it has either a 'Z', '+', or '-', and does not contain an 'e' or a '.'.  However, all forms of it are ASCII-sortable for the next 7988 or so years.  Personally, I prefer the Internet standard date-time strings version, but to each his own.

    @blakeyrat said:

    There's actually a sensible way of storing dates this way, the Julian Day Count. I use it in my library to find the Mayan date, since it's by far the best way of counting dates before the Julian->Gregorian conversion. (Of course this problem will be substantially easier past December, 2012, since the Long Count has a new epoch...)

    The difference, of course, is that the Julian Day Count is:
    1) An accepted standard
    2) Well-documented
    3) Developed by historians and archeologists in support of scientific projects
    4) Backed by decades of productive use in solving problems

    Which one?  The Julian Day, the Reduced Julian Day, the Modified Julian Day, the Truncated Julian Day, the Dublin Julian Day, the Chronological Julian Day, the Lilian day, the ANSI Date, the Heliocentric Julian Day, or something else I missed on that page?  I'm confused.  (I'd guess one of the ones that actually have 'Julian day' as part of their name, but...)  In any event, it's interesting how easy those are to use to figure out what year, month, and day something happened.

    Disclaimer: yes, I actually am aware you are talking about the first one, as it's an exact match.  I am, however, pointing out that there's over half a dozen competing standards which all look the same, the only difference between them is what their zero point is.  *That* is a huge mess I don't want to go anywhere near.  At least with ISO 8601, there's only one competing date format that looks the same (specifically yyyy-dd-mm), and IIRC I've only heard of a single proponent for that one.  I haven't heard of any conflicting standards for RFC3339.

    One can annotate the Julian Date to say which one you're using, except that annotation is a letter sequence, so won't fit in your float.  Oh, and Julian Date is sometimes used as a moronic way of saying "day of year", so I really needed to say Julian Day back there.



  • @tgape said:

    Which one? The Julian Day, the Reduced Julian Day, the Modified Julian Day, the Truncated Julian Day, the Dublin Julian Day, the Chronological Julian Day, the Lilian day, the ANSI Date, the Heliocentric Julian Day, or something else I missed on that page? I'm confused. (I'd guess one of the ones that actually have 'Julian day' as part of their name, but...) In any event, it's interesting how easy those are to use to figure out what year, month, and day something happened.

    Well, like all Wiki articles, it's a confusing mess of shit.

    But the section named "alternatives" is, in this case, actually talking about alternatives to the Julian Day Count. If they deleted that section, the article would be about 15 times clearer.

    My point was, if you really, really, really want to store a particular date/time as a fractional number, there's already a well-worn standard for that. It's just a standard most people haven't been exposed to, because it's primarily useful in fields where you need to accurately measure dates before 1582. The Wiki article even includes the exact psuedo-code you need to convert back and forth.

    @tgape said:

    Disclaimer: yes, I actually am aware you are talking about the first one, as it's an exact match.  I am, however, pointing out that there's over half a dozen competing standards which all look the same, the only difference between them is what their zero point is.  That is a huge mess I don't want to go anywhere near.  At least with ISO 8601, there's only one competing date format that looks the same (specifically yyyy-dd-mm), and IIRC I've only heard of a single proponent for that one.  I haven't heard of any conflicting standards for RFC3339.

    Yes, but the entire FUCKING POINT of my post was that there is a sane way of storing dates as a FLOAT. If you're not using a float, and following ISO 8601, FINE! THAT'S GREAT! LOVELY! I WOULD RECOMMEND DOING THAT! OBVIOUSLY DOING THINGS THE RIGHT WAY IS BEST, DUH!

    But to criticize my post because of that means (drum roll please) you missed the fucking point!

    @tgape said:

    One can annotate the Julian Date to say which one you're using, except that annotation is a letter sequence, so won't fit in your float.  Oh, and Julian Date is sometimes used as a moronic way of saying "day of year", so I really needed to say Julian Day back there.

    1. There's only one, Wiki articles are all shit, you should know this by now, don't be a fucking dumbass, christ

    2) That's why I refer to it as "Julian Day Count", because I think its less confusing.



  • Daww, you disappoint. I was hoping you'd comment on the usage of floating point (usually double precision numbers) for tracking time between frames (and often elsewhere) in games and how it leads to the game world breaking down after a few months. Daww.



  • @blakeyrat said:

    @tgape said:
    Which one? The Julian Day, the Reduced Julian Day, the Modified Julian Day, the Truncated Julian Day, the Dublin Julian Day, the Chronological Julian Day, the Lilian day, the ANSI Date, the Heliocentric Julian Day, or something else I missed on that page? I'm confused. (I'd guess one of the ones that actually have 'Julian day' as part of their name, but...) In any event, it's interesting how easy those are to use to figure out what year, month, and day something happened.

    Well, like all Wiki articles, it's a confusing mess of shit.

    But the section named "alternatives" is, in this case, actually talking about alternatives to the Julian Day Count. If they deleted that section, the article would be about 15 times clearer.

    Agreed, but that still leaves this:

    @Wikipedia_Julian_day said:

    Julian day is used in the Julian date (JD) system of time measurement for scientific use by the astronomy community, presenting the interval of time in days and fractions of a day since January 1, 4713 BC Greenwich noon.

    @Wikipedia_Julian_calendar said:

    The Julian calendar began in 45 BC (709 AUC) as a reform of the Roman calendar by Julius Caesar.

    Also, Year numbering.  Gah.  What a confusing mess.

    But, in any event, I can't find any indication of why 4713 was considered a good starting point.  It may have had something to do with a Ptolemy.  At least the Julian day doesn't use the proleptic Gregorian calendar.



  • @tgape said:

    Agreed, but that still leaves this:

    @Wikipedia_Julian_day said:

    Julian day is used in the Julian date (JD) system of time measurement for scientific use by the astronomy community, presenting the interval of time in days and fractions of a day since January 1, 4713 BC Greenwich noon.

    @Wikipedia_Julian_calendar said:

    The Julian calendar began in 45 BC (709 AUC) as a reform of the Roman calendar by Julius Caesar.

    ... and? What's your point? I don't see where the confusion lies... calendars don't begin at their epoch, generally. People didn't start using the Gregorian calendar on Jan 1, 0001 AD. The Mac Classic's epoch was 1904, but Macs didn't exist in 1904... etc. (Actually, the confusion is terrible Wikipedia wording. "Begins" meaning "people began using it" = 45 BC. "Begins" meaning "the earliest date recordable" = 4713 BCE.)

    That said the 4713 epoch is something of a WTF, but... it's pretty much arbitrary, and it's far enough back that virtually all historical events that have precise dates are positive numbers, which works fine. (Same applies to the Mayan Long Count epoch in 3114 BCE, really. No reason we couldn't use that.)

    Edit: oh and the Mayans almost certainly didn't have a fully-functional calendar in 3114 BCE. Like everybody else, they picked an epoch long before they actually started counting years.


  • Discourse touched me in a no-no place

    @tgape said:

    But, in any event, I can't find any indication of why 4713 was considered a good starting point.  It may have had something to do with a Ptolemy.  At least the Julian day doesn't use the proleptic Gregorian calendar.
    For those interested:
    Why was 4713 BCE used as the starting date for the Julian period? Scaliger chose 12:00 UT, 1 January of that year for Julian day 0.0 because it was the nearest past year when all three cycles, solar, Metonic, and indiction, exactly coincided. The present Julian period will end at 12:00 UT, 31 December 3267.



  • @nexekho said:

    Daww, you disappoint. I was hoping you'd comment on the usage of floating point (usually double precision numbers) for tracking time between frames (and often elsewhere) in games and how it leads to the game world breaking down after a few months. Daww.
     

    You should see what happened at the world edge in the previous version of Minecraft's world engine. Laws of physics basically broke down as its quantum resolution got coarse enough for macroscopic effects to manifest. Shit was crazy enough to be perceived as 'epic' by the community who branded this region "The Far Lands", with the colourfully named subtypes The Loop and The Stack. (disclaimer: that's a wiki link. I am not responsible for time wasted as a result of compulsive article link clicking)



  • @dhromed said:

    You should see what happened at the world edge in the previous version of Minecraft's world engine

    We've been there on my cheats server using edited warps, it lags my machine (which runs two servers as background tasks) to hell. And makes Cartograph crash when you try and build a map of it.



    Somewhat regretting it. Not sure why I don't have any screenshots of it. Here's a screenshot of us striking pigs with lightning and making them into monsters instead.



    [img]http://i.imgur.com/vCYBhl.jpg[/img]



    I consider the use of floats to track a world made of fixed point units odd. Granted, GL is pretty much float or get out with matrices and such, but it still doesn't sit right with me.



  • so many pigs

    so much porkchops

    I cannot consume them all

    That reminds me of the video I saw where a guy topped up 6 dispensers with eggs and thus made a rapid-fire chicken cannon.

    And that in turn reminds me of a guy who constructed a simple repeater contraption as a high-frequency pulse generator. He then hooked it up to a dispenser topped up with arrows. That was nice.

    This, again, reminds me of a guy who built a massive floodgate and tons of farmland next to it. It was an epic mass harvester.

     

    But, on topic,  it may be in people's best interest if it were ported to C++. Java doesn't strike me as the most potimal language to do this sort of stuff in. But despite that, it's freakishly amazing what mods such as GLSL + Water + Realistic texture can do if the geometry isn't too difficult and the GPU has time for fancy things.

    I crave these mods.



  • @dhromed said:

    But, on topic,  it may be in people's best interest if it were ported to C++. Java doesn't strike me as the most potimal language to do this sort of stuff in. But despite that, it's freakishly amazing what mods such as GLSL + Water + Realistic texture can do if the geometry isn't too difficult and the GPU has time for fancy things.

    Most of the graphics overhead in Minecraft is because it uses display lists instead of simple vertex/indice lists and doesn't merge faces resulting in a huge amount of wasted driver time thus wasted GPU time.



  • @dhromed said:

    This, again, reminds me of a guy who built a massive floodgate and tons of farmland next to it.
     

    William of Orange?



  • @dhromed said:

    But, on topic,  it may be in people's best interest if it were ported to C++. Java doesn't strike me as the most potimal language to do this sort of stuff in. But despite that, it's freakishly amazing what mods such as GLSL + Water + Realistic texture can do if the geometry isn't too difficult and the GPU has time for fancy things.
     

    For me nothing quite tops the acid trip shader mod.

     


Log in to reply