MySQL timestamps:
- Are stored in UTCThey are converted to UTC on storage and converted back to your time zone on retrieval. If youchange time zone settings, the retrieved values also change.
- Can be automatically initialised and updatedYou can set their default value and / or auto update value to
CURRENT_TIMESTAMP - Have a range of
1970-01-01 00:00:01 UTCto2038-01-19 03:14:07 UTC
Whereas MySQL datetime:
- What you store is what you get ™.
- Have a range of
1000-01-01 00:00:00to9999-12-31 23:59:59Values outside the range may work - but only values within the range are guaranteed to work. - You can store dates where day or month is zero.This is the MySQL way of storing birthdays! You can't do that with a
TIMESTAMP, with the only exception being the zero value of0000-00-00. - You can set default value to
NOW()in some instances, but the preferable and more natural way of automatically initialised and updated dates isTIMESTAMP.
And of course there are also
DATE and TIME, which work just like DATETIME, but of course DATEdoesn't care for time and TIME doesn't care for date. All four data types work perfectly with the wide array of date and time functions, but when you are mixing data types you should be aware of conversion effects.
Now, to your question: You should use
DATETIME everywhere. Not for technical reasons, but since you are still unclear on how MySQL works, DATETIME is the simpler choice. This will mean that you will have to calculate the current timestamp in PHP before storing, that's as easy as:$mysqldate = date("Y-m-d H:i:s"); string date ( string $format [, int $timestamp = time() ] )
The
"Y-m-d H:i:s" format is the one compatible with MySQL and by leaving the second parameter empty, date() calls time() to get the current UNIX timestamp.
Using a
TIMESTAMP instead of a DATETIME has the added value of MySQL taking over the responsibility of deciding on the current timestamp, and you can skip the field when you are inserting / updating. But since you are already sending a query, and the code to get the current timestamp in PHP is minimal, you can safely go with DATETIME for everything.
As for the actual code to store the PHP timestamp into the database, you should look at PHP Data Objects, and if you are still unclear, ask on StackOverflow instead. But there are almost 1.5k related questions already, make sure you go through them before asking. Just a hint, prepared statements is how the cool
Datetime format could be a pain when you want to calculate something (difference between 2 dates, get yesterday's date from current date, get 1 week ago from current date, get tomorrow date or something like that.) It's not really hard but you will usually need to convert them in
timestamp and then do the stuff. And yes current datetime for example you get with: $current = date("Y-m-d"); or $current = date("Y-m-d H:i:s"); if you also want hours, minutes and seconds.
Timestamp is just a number 11 digit long which doesn't really "mean" anything. When you look at it you will not know which date it represent. So it's not really user friendly and you can't use it to just echo it and show it to your visitors for example. You will need to "transform" it into something readable. But it's also offers you an ability to easily convert it, and to calculate different dates. For example if you want to get timestamp for tomorrow at the same time you just add number of seconds to current date and you have tomorrow time stamp. Example:
$tomorrow = time()+24*3600; You can also easily get a difference between two dates in seconds or anything like that.
So i would suggest to use both datetime and timestamp. Even if you use for example PhpMyadmin and want to quickly look at some data it will be easier for you when you see a date in date time format (example:
2011-12-20 10:15:20) as it will be if you just look at the 11digit number. So use both and then call and use the one which suits you better.
Timestamps in MySQL generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.
If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.
The main difference is that DATETIME is constant while TIMESTAMP is affected by the
time_zonesetting.
So it only matters when you have — or may in the future have — synchronized clusters across time zones.
In simpler words: If I have a database in Australia, and take a dump of that database to synchronize/populate a database in America, then the TIMESTAMP would update to reflect the real time of the event in the new time zone, while DATETIME would still reflect the time of the event in the au time zone.
A great example of DATETIME being used where TIMESTAMP should have been used is in Facebook, where their servers are never quite sure what time stuff happened across time zones. Once I was having a conversation in which the time said I was replying to messages before the message was actually sent. (This, of course, could also have been caused by bad time zone translation in the messaging software if the times were being posted rather than synchronized.
In MYSQL 5 and above, TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, and not for other types such as DATETIME.)
By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis, as described here: MySQL Server Time Zone Support
Share:
No comments:
Post a Comment