UTC vs PST

Databases
1

Hi,

I’m building an application and am getting a little bit stuck on the best way to store dates/times in my MySQL database. After reading lots of posts, including this one on Sitepoint, I’ve decided to go with the DATETIME instead of the TIMESTAMP since it’s more readable. Users of my application will be international, which begs the question, which timezone should I use to store the DATETIME?

I live in PST, so my gut tells me to go with PST and then just adjust the time depending on my users’ timezones. The other option is to store it in UTC, and just get used to the difference between UTC and PST when I need to look at dates in my database.

Am I making a mountain out of a mole hill (PST vs UTC) or does one make sense over the other?

2

FYI the mysql TIMESTAMP datatype is just as readable as DATETIME – you’re thinking of INTEGER storing a unix timestamp

note that TIMESTAMP value actually converts to a unix integer when it is stored, and converted back when it is retrieved

also, it converts to UTC automatically (according to the manual… disclaimer: i have never tested this)

3

And what about PDT? You could end up one hour off or need to change it twice a year.

UTC doesn’t change with the seasons.

Is this critical for your needs, or more an “I’d rather not need to convert it” issue?

1 Like
4

Perhaps you’re confusing MySQL TIMESTAMP with PHP Unix timestamp?

Admittedly, I’d have extreme trouble recognizing something like
1272508903
as representing
2010-04-28 22:41:43

5

I am definitley thinking that I’d prefer not to look at “1272508903” in the database (PHP Unix TIMESTAMP). However, I think that I was in fact a bit confused. After r937’s comment above, I just checked out this Stackoverflow post which goes into things a little bit deeper.

And I definitely didn’t have the full picture asI thought that it was either the integer representation or the DATETIME. What I didn’t realize was that there was the MySQL TIMESTAMP as referenced above by r937 and described in the Stackoverflow post which was human readable AND a fixed point in time.

Also, I hadn’t even thought about the daylight savings time issue. (grrrrrrr…). It sounds then, that having a human readable entry with ease of computational use would be the mysql TIMESTAMP. Would this be the consensus if my use will be for:

  1. stamping updates/inserts in my database
  2. starting and ending dates of courses (it’s an online course app)
  3. computing time left in “free trial” periods.

My final related question: if you guys agree that the MySQL TIMESTAMP would be my best bet, what the heck am I (and others?) going to do when 2038 comes around?

6

AFAIK that is one key difference between DATETIME and TIMESTAMP

Remember that although DATETIME, DATE, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. For example, TIMESTAMP values cannot be earlier than 1970 or later than 2038. This means that a date such as ‘1968-01-01’, while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0.

7

[quote=“kreut, post:5, topic:192433, full:true”]… what the heck am I (and others?) going to do when 2038 comes around?[/quote]same thing we old COBOL guys did in the year 2000

8

Thanks, Rudy (r937)! :smile:

This discussion has been very helpful to me, and hopefully it will be helpful to others, and I’m going to go with the mysql TIMESTAMP and see what happens when 2038 rolls around.

Cheers,

Eric

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.