Jump to content

How to store time in mysql?


SGUserFace

Recommended Posts

There are dozens of useful datetime functions within mysql. These use DATE, DATETIME or TIMESTAMP types. To use them with time() format times would require conversion every time, reducing efficiency. Plus they have the advantage of being human-readable which unix style timestamps do not. They also have a greater value range than time().

Link to comment
Share on other sites

If you need to represent a particular moment in time rather than just the idea of one then I suggest TIMESTAMP and not DATETIME; for example, a moment in time might be when an event happened while an idea would be someone's daily schedule - and yes, that's kinda vague. Then make sure your connection is setting an appropriate timezone: UTC, your web server's timezone, or your user's timezone. Your code should be running on that same timezone too.

 

Timezones can be a bitch. The main advantage of TIMESTAMP over DATETIME is that with a TIMESTAMP MySQL will show you the time according to the current timezone but with DATETIME all you have is a string with no guarantee of when that time actually was. Like with a TIMESTAMP you know that "2017-10-06 12:34:56" was about a half-hour after midnight according to the current timezone at this moment, while with a DATETIME you know it was a half-hour after midnight according to whatever unknown timezone was being used at the time the date was calculated.

 

Meanwhile folks like PostgreSQL and Oracle have the concept of a timestamptz type...

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.