CODEWITHSUNDEEP
phpmysql
user6464914
user6464914

Reputation:

the most accurate way to store date and time in mysql database

What is the best way to store date and time in the database? I tried using now() on localhost: it works, but on live server it counts far back as 8 hours ago and current timestamp does the same thing. What is the most accurate approach?

$d="INSERT INTO `users`(`time`) VALUES (now())";

Upvotes: 0

Views: 113

Answers (2)

Kavaliauskas Ivan
Kavaliauskas Ivan

Reputation: 1

The only thing that you had wrong was your time zone

For example, use this :

SET time_zone = 'America/New_York';

with your time zone

Upvotes: 0

T0xicCode
T0xicCode

Reputation: 4951

You are storing the time in the most accurate method. It might not be in the correct timezone, but it is (as far as the server can tell) exactly that time when saved. If the timezone is incorrect, you will have to tell MySQL which timezone is considered "local" by executing something like SET time_zone = 'YOUR_TIME_ZONE' as soon as you open the connection.

A good trick to help with dealing with times is to make your storage layer only deal with times in UTC, and translate it to the local timezone in the presentation layer.

Upvotes: 1

Related Questions