Page 1 of 1
Storing dates in different formats.
Posted: Mon Jun 19, 2006 2:57 pm
by Benjamin
Ok what is the deal here.
People Like MM/DD/YYYY
MySQL Likes YYYY/MM/DD
PHP Likes "seconds since the epoch" (when adding and subtracting dates)
The Sun Likes Leap Years
The World likes different time zones.
What is the best way to link these all together? Is it best just to store everything as a unix timestamp rather than mess with all the conversions? Are there any disadvantages to doing that?
Posted: Mon Jun 19, 2006 3:02 pm
by feyd
Depends a bit on how it's going to be used. In databases I keep the date in a DATE field because it's native. When dealing in varying output forms (user defined) I keep unix timestamps so I don't have to waste time converting. My own personal preference is Julian timestamps (ramped up to a higher precision than "normal")
Posted: Mon Jun 19, 2006 3:11 pm
by Luke
feyd wrote:My own personal preference is Julian timestamps (ramped up to a higher precision than "normal")
How come?
Posted: Mon Jun 19, 2006 3:12 pm
by feyd
much much much much larger range of dates covered.
Posted: Mon Jun 19, 2006 3:15 pm
by Weirdan
People Like MM/DD/YYYY
That depends on local traditions. Personally, I like DD.MM.YYYY, that's how we write it here in ex-USSR.
Posted: Mon Jun 19, 2006 3:15 pm
by Benjamin
Could you make a code snippet and post it if you get a chance?
Posted: Mon Jun 19, 2006 3:16 pm
by Luke
I was having a difficult time finding anything by typing julian timestamp into google or wiki but finally found this:
November 17, 1858 - VMS epoch and the base date of the Modified Julian Day used in celestial ephemerides by the United States Naval Observatory and other astronomy organizations.
I see the advantage now. Are there special functions to work with this type of timestamp? I can hardly find any information about it.
Posted: Mon Jun 19, 2006 3:36 pm
by feyd
The Ninja Space Goat wrote:I was having a difficult time finding anything by typing julian timestamp into google or wiki but finally found this:
November 17, 1858 - VMS epoch and the base date of the Modified Julian Day used in celestial ephemerides by the United States Naval Observatory and other astronomy organizations.
I see the advantage now. Are there special functions to work with this type of timestamp? I can hardly find any information about it.
There's a whole bunch of functions in PHP for it..
check
juliantojd()
Posted: Mon Jun 19, 2006 3:45 pm
by Christopher
I store everything YYYY-MM-DD and reformat to display. For calculations I ususally try frst to get the database to do the work, otherwise I convert to timestamps. I also sometimes use the shortcut for compariing dates of using YYYYMMDD and just treating them as numbers.
Posted: Mon Jun 19, 2006 6:49 pm
by Ambush Commander
Wow. I'm suprised no one uses plain old Unix timestamps. Although I suppose using Julian timestamps would
circumvent the Year 2038 problem.
Timestamp is a misleading term for Julian Day Count: it implies it's measured in seconds (this is because we often use timestamp to refer to the Unix timestamp). Feyd, how would you ramp up precision? And also, is it difficult explaining to other developers what this Julain Day Count means? (although I suppose if you use a Date class, all such problems disappear.)
Posted: Mon Jun 19, 2006 7:48 pm
by feyd
Ambush Commander wrote:Timestamp is a misleading term for Julian Day Count: it implies it's measured in seconds (this is because we often use timestamp to refer to the Unix timestamp). Feyd, how would you ramp up precision? And also, is it difficult explaining to other developers what this Julain Day Count means? (although I suppose if you use a Date class, all such problems disappear.)
Yes, timestamp is slightly misleading.. I was only using the terms from the original request.
As far as ramping up precision goes, the Julian year is less accurate in it's progression than the Gregorian, so to bring it inline, some math is in order. It's been a while since I looked at my Julian class but basically it added even more precision for time correction (Earth orbital progression) that went so far as to fix up to the year 4000 leap year "bug." A fairly simple calculation in itself, but a calculation even native PHP will not make, yet. Another addition I made was storing the fractional time within the day. In the last build I did of it, I believe the precision was out to 100 decimal places. I used bcmath for all the calculations to make sure precision didn't fall off with larger numbers.
I should probably write a new version of it at some point to add in functionality and alter things to how I roll today. Honestly, I haven't used it in months as I rarely need to keep track of time points outside of the current unix-timestamp (plus microtime) range.