Page 1 of 1
DATETIME vs TIMESTAMP
Posted: Fri Jun 16, 2006 4:10 pm
by BigAbe
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 --
Posted: Fri Jun 16, 2006 4:28 pm
by Oren
Timestamp, since it can be used directly in the PHP date-related functions.
Posted: Fri Jun 16, 2006 5:22 pm
by RobertGonzalez
There are some key differences, the most notable being...
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.
This came from
the DATETIME, DATE and TIMESTAMP page of the MySQL Manual.
Posted: Fri Jun 16, 2006 6:25 pm
by feyd
Oren wrote:Timestamp, since it can be used directly in the PHP date-related functions.
No, it can't. MySQL does not return, by default, a unix timestamp for the TIMESTAMP field. It's formatted, exactly like DATETIME.
The thing with TIMESTAMP is, when the record is updated, so is the TIMESTAMP (automatically) unless you explicitly set it in the query.
Posted: Fri Jun 16, 2006 7:56 pm
by Roja
I use datetime with the format:
Posted: Fri Jun 16, 2006 8:05 pm
by bdlang
feyd wrote:
The thing with TIMESTAMP is, when the record is updated, so is the TIMESTAMP (automatically) unless you explicitly set it in the query.
...and this is why I normally use TIMESTAMP type.
Posted: Sat Jun 17, 2006 3:30 am
by Oren
feyd wrote:No, it can't. MySQL does not return, by default, a unix timestamp for the TIMESTAMP field. It's formatted, exactly like DATETIME.
Oh no, you got me wrong. I keep the timestamp in an INT field

Posted: Sat Jun 17, 2006 4:53 am
by Oren
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

Posted: Sat Jun 17, 2006 12:59 pm
by duk
i always define my column as TEXT and when inserting information i just use date() function... for me this is the easy way and the most simple, i dont know if there is more complex problems for more professional applications in case using just date functions less then TIMESTAMP Mysql Function???
Posted: Sun Jun 18, 2006 9:26 am
by Oren
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

Anyone?
P.S First time ever to quote myself

Posted: Sun Jun 18, 2006 12:15 pm
by bdlang
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.