DATETIME vs TIMESTAMP
Moderator: General Moderators
DATETIME vs TIMESTAMP
I know there's not too much of a difference between DATETIME and TIMESTAMP (correct me if I'm wrong here).
I'm just curious as to any good recommendations of which to use and why.
Any thoughts?
-- Abe --
I'm just curious as to any good recommendations of which to use and why.
Any thoughts?
-- Abe --
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
There are some key differences, the most notable being...
This came from the DATETIME, DATE and TIMESTAMP page of the MySQL Manual.The MySQL Manual wrote:Remember that although DATETIME, DATE, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. For example, TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
No, it can't. MySQL does not return, by default, a unix timestamp for the TIMESTAMP field. It's formatted, exactly like DATETIME.Oren wrote:Timestamp, since it can be used directly in the PHP date-related functions.
The thing with TIMESTAMP is, when the record is updated, so is the TIMESTAMP (automatically) unless you explicitly set it in the query.
I use datetime with the format:
Code: Select all
date("Y-m-d H:i:s");I've just read this tutorial: http://www.phpit.net/article/handling-d ... php-mysql/, and I may change my mind and move back to DATETIME (yes 'move back' as I used DATETIME at first).
Before I do that, I want to know what are the advantages of using DATETIME. I'm not a professional when it comes to MySQL... What can be done with MySQL built-in functions when you have a DATETIME field? I'm talking about things that cannot be done with PHP or that at least better to be done with MySQL (where better = faster, use less resources and, especially, makes use of a simple syntax to perform relatively complex queries).
Thanks in advance
Before I do that, I want to know what are the advantages of using DATETIME. I'm not a professional when it comes to MySQL... What can be done with MySQL built-in functions when you have a DATETIME field? I'm talking about things that cannot be done with PHP or that at least better to be done with MySQL (where better = faster, use less resources and, especially, makes use of a simple syntax to perform relatively complex queries).
Thanks in advance
Anyone?Oren wrote:I've just read this tutorial: http://www.phpit.net/article/handling-d ... php-mysql/, and I may change my mind and move back to DATETIME (yes 'move back' as I used DATETIME at first).
Before I do that, I want to know what are the advantages of using DATETIME. I'm not a professional when it comes to MySQL... What can be done with MySQL built-in functions when you have a DATETIME field? I'm talking about things that cannot be done with PHP or that at least better to be done with MySQL (where better = faster, use less resources and, especially, makes use of a simple syntax to perform relatively complex queries).
Thanks in advance
P.S First time ever to quote myself
Oren>
To answer your question about the advantages to using DATETIME
Quick answer - MySQL manual: Date and Time Functions.
Better answer (maybe) -
I've relied on MySQL's built in TIMESTAMP type for storing...well, timestamps.
When I want to grab the record's date and / or time, I can use MySQL's DATE_FORMAT() to return a nicely formatted string to be displayed in the PHP output. I can also use MySQL's UNIX_TIMESTAMP() function to return the standard UNIX epoch value from the column, and then utilize it in the script in places where that is of value rather than a formatted date. MySQL's UNIX_TIMESTAMP() function returns the same value (UTC) as PHP's time(). The TIMESTAMP type field can be set to auto-update (the default value for the first TIMESTAMP type column in your table) so you don't have to pass a value to it at all.
I find this to be alot more simple than monkeying with transferring dates and times between MySQL and PHP. Is it better? Is it universal? Probably not.
As far as the article is concerned, I found the blog entry it was based upon to be a bit more lucid and informative. I can honestly say my applications thus far have not dealt with alot of timezone issues so I can't vouch for or comment on the material.
To answer your question about the advantages to using DATETIME
Quick answer - MySQL manual: Date and Time Functions.
Better answer (maybe) -
I've relied on MySQL's built in TIMESTAMP type for storing...well, timestamps.
I find this to be alot more simple than monkeying with transferring dates and times between MySQL and PHP. Is it better? Is it universal? Probably not.
As far as the article is concerned, I found the blog entry it was based upon to be a bit more lucid and informative. I can honestly say my applications thus far have not dealt with alot of timezone issues so I can't vouch for or comment on the material.