Storing dates in different formats.
Moderator: General Moderators
Storing dates in different formats.
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?
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?
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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")
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.
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.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
There's a whole bunch of functions in PHP for it..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.
check juliantojd()
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
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.)
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.)
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Yes, timestamp is slightly misleading.. I was only using the terms from the original request.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.)
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.