CODEWITHSUNDEEP
mysqldatetime
threepoint1416
threepoint1416

Reputation: 132

What MySQL time format should I use

The server time changes every now and then (maybe because of the cloud?). I am using the NOW() function for the table columns with the format DATETIME. Retrieving an element that has a date feature is pretty confusing because of the wrong date or time.

I tried adjusting the time computation by adjusting the value of the time by adding or subtracting a certain amount of hours. That of course will need the location of the server at that moment the NOW() was executed and also the user's browser time (deriving the UTC offset). The problem arises when the server changes location (I don't know how that is possible at this point). The date computation will be erroneous for the previous elements that used the NOW() function.

What is the better time/date format when inserting an element with a date+time attribute? (And for an extra, can you please expound and give test cases for this?). TIMESTAMP perhaps? (I am horribly agitated that I will have to change all the previous values and table attributes).

#DatabaseDesignFail

Upvotes: 1

Views: 85

Answers (1)

cbranch
cbranch

Reputation: 4769

Why not stick with DATETIME, but store UTC timestamps to eliminate the server's timezone from the equation? Use UTC_TIMESTAMP() instead of NOW().

Upvotes: 1

Related Questions