SGUserFace Posted October 5, 2017 Share Posted October 5, 2017 How to store time in mysql? PHP time() mysql now() Quote Link to comment Share on other sites More sharing options...
Barand Posted October 5, 2017 Share Posted October 5, 2017 Time as in "time of day" or as in duration? Quote Link to comment Share on other sites More sharing options...
SGUserFace Posted October 5, 2017 Author Share Posted October 5, 2017 Time as in "time of day" or as in duration? As created on Quote Link to comment Share on other sites More sharing options...
Barand Posted October 6, 2017 Share Posted October 6, 2017 Use mysql TIMESTAMP oor DATETIME type column. Quote Link to comment Share on other sites More sharing options...
SGUserFace Posted October 6, 2017 Author Share Posted October 6, 2017 Use mysql TIMESTAMP oor DATETIME type column. What is the reason? Why not with PHP? time() Quote Link to comment Share on other sites More sharing options...
Barand Posted October 6, 2017 Share Posted October 6, 2017 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(). Quote Link to comment Share on other sites More sharing options...
requinix Posted October 6, 2017 Share Posted October 6, 2017 (edited) 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... Edited October 6, 2017 by requinix Quote Link to comment Share on other sites More sharing options...
Barand Posted October 6, 2017 Share Posted October 6, 2017 ... you know that "2017-10-06 12:34:56" was about a half-hour after midnight according to the current timezone, while with a DATETIME you know it was a half-hour after midnight Half an hour after midday in my timezone. You're right, they are a bitch Quote Link to comment Share on other sites More sharing options...
requinix Posted October 6, 2017 Share Posted October 6, 2017 Half an hour after midday in my timezone. You're right, they are a bitch ...er, yeah, that one. It took me a full five minutes and opening and closing this page twice to figure out what you meant Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.