Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
1  
Will the offset (TINYINT) store hours or minutes? The timezone offset can be half an hour or 15 minutes. –  ypercube Jun 6 '12 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 '12 at 19:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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