Storing dates in different formats.

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Storing dates in different formats.

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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")
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

feyd wrote:My own personal preference is Julian timestamps (ramped up to a higher precision than "normal")
How come?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

much much much much larger range of dates covered.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Could you make a code snippet and post it if you get a chance?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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()
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post 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.)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply