DATETIME vs TIMESTAMP

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply

DATETIME or TIMESTAMP

DATETIME
3
33%
TIMESTAMP
6
67%
 
Total votes: 9

BigAbe
Forum Commoner
Posts: 66
Joined: Fri Mar 31, 2006 7:41 pm

DATETIME vs TIMESTAMP

Post 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 --
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

Timestamp, since it can be used directly in the PHP date-related functions.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Post by Roja »

I use datetime with the format:

Code: Select all

date("Y-m-d H:i:s");
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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 :wink:
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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 8)
duk
Forum Contributor
Posts: 199
Joined: Wed May 19, 2004 8:45 am
Location: London

Post 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???
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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 8)
Anyone?

P.S First time ever to quote myself :D
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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.
Post Reply