1

Maye I am wrong to use now() to get timestamps when storing data?

When I display date/time to the user, of course he wants to see a local time, and if he inputs time related data, rather than me using now() then he will be inputting local date/time.

Why code is getting muddled with conversations - what's the best practice for handling timestamps? UTC/locla time? How & when to adjust?

Mawg says reinstate Monica
  • 38,334
  • 103
  • 306
  • 551

2 Answers2

2

Just store all the dates in timestamp mysql column type, which can handle all timezone issues, and mysql will do all work for you.

So in the begin of your application startup all you need is to specify what timezone you need the dates belongs towith query:

SET time_zone='Asia/Vladivostok'

For example.

Also, in this case you should not get any timestamps from php, if you need to insert current time - you have to use mysql's NOW().

That's all.

zerkms
  • 249,484
  • 69
  • 436
  • 539
1

Store all the dates in one time zone so they're consistent. UTC/GMT+0 is good for this.

Then use CONVERT_TZ to convert input to UTC/GMT+0 or from UTC/GMT+0 to a user's time zone.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Since all you've asked about are SQL functions, you should tag the question with the RDBMS you're using (MySQL?) instead of PHP.

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
  • +1 And I have now tagged it ODBC (I thought it was a PHP question, but apparently not). If I convert everything to UTC then it _is_ consistent, but as I said in the question, I have to convert everything going in & coming out of the d/b, which is a pain (especially if I miss one). Since there are only 2 answers and the other seems to contradict this one, I will assume that there is no standard "best practice". – Mawg says reinstate Monica Nov 14 '10 at 23:11