3

I faced problem with timezone on my last two projects. So What is best way to sort out problems with timezone on db design.

I mean, What format should i added date time stamp.

On of my problem. My client asked me to expire particular event on users timezone. How can i solve this.

Thanks in advance.

Improve this question

1 Answer 1

Reset to default
2

Off the top of my head, the simplest thing I could think of would be to store three values:

  • UTC (DATETIME),
  • timezone offset for datetime in minutes (SMALLINT) (-1440 to 1440)
  • DST (daylight saving time), 0 or 1 (TINYINT)

I originally was thinking of timezone offset in hours. @yercube reminded me of other places with 15 min or 30 min additional offsets . For example, these cities have these times:

  • New York City at 4:00 PM
  • Bangalore 1:30 AM next day
  • Katmandhu 1:45 AM next day

You would have to

  • perform date arithmetic to handle display of datetimes
  • compare datetimes as pure UTC values
Improve this answer
2
  • 1
    Will the offset (TINYINT) store hours or minutes? The timezone offset can be half an hour or 15 minutes.
    – ypercubeᵀᴹ
    Jun 6, 2012 at 19:50
  • @ypercube Sorry, I was thinking in hours. You are right because Bangalore, India is on 1:24 AM right now (3:54 in NYC). So make timezone offset in minutes. I'll adjust my answer.
    – RolandoMySQLDBA
    Jun 6, 2012 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.