How common are TIMESTAMPS over DATETIME fields?

Question

I want my product, like every other web product out there, to pull a data information from my database and convert it to the user's timezone before displaying it on a page. I am using PHP and MySQL.

Is the use of TIMESTAMP fields more common than the use of DATETIME fields?

From my research, it seems that using DATETIME fields, I would have to

1. Store all date information in UTC
2. call date_default_timezone_set('UTC') each time the application starts
3. manually subtract the user's UTC offset hours from dates created using date() being based on the current date/time
4. manually subtract the user's UTC offset hours from existing dates passed to and formatted by date()

Using TIMESTAMPS for my date fields, it seems I will only have to run the following when a database connection is made: SET time_zone='the users timezone' (eg. America/Los_Angelos).

Considering this, I would deduce most people would be inclined to using TIMESTAMPs rather than DATETIMEs...is this accurate?

Answer 1

From this location:

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

Edit: to answer your question, DATETIME is more commonly used, as TIMESTAMP is not intended to be used for storing general Date / Time information.

Answer 2

TIMESTAMP is special in that it can be autoupdated on created or row change. See the timestamp docs.

Basically, unless you want automagic behaviour on the part of MySQL, use DATETIME. DATETIME can also represent a larger range.

Answer 3

The TIMESTAMP field is commonly used to support concurrency checking. When a table record is being modified by a user, through a form or some other mechanism, the database knows that it is updating the same record that the user just read because the TIMESTAMP that the user is submitting with his record changes still matches the TIMESTAMP in the database record.

If another user modifies the record prior to the first user performing a save, the TIMESTAMP that the first user read would no longer match the TIMESTAMP that is in the database. What happens next depends on how the system handles concurrency violations. Some systems will prevent the save. Other systems will ask the first user if they want to overwrite the other person's changes.

Answer 4

No, I don't think that's accurate.

Generally, you use datetime to store just that - the date and time that something is happening, when it's entered into your site. You use timestamp to keep track of when data was entered into your site for your program's use. timestamp shouldn't have to keep track of time zones because all of the work happens in your db server's time zone - whereas datetime has more options for presenting it properly for it to make sense as output to the user.

So, for the question "Which is more common for presenting user-timezone data", I'd say the answer is DATETIME.

Answer 5

If you need to do time zone manipulation, and you don't need to store dates before 1970 or after 2038, use TIMESTAMPs.

You can do a single SQL statement:

set timezone='America/New_York';

And all dates retrieved from the database will be in EDT.

I would avoid doing UTC offset math as it becomes difficult to handle all of the cases correctly. Your best bet is to set the PHP and MySQL timezones appropriately for each user.

TIMESTAMPs store dates ONLY as UTC, and MySQL handles the translation for it.

When storing dates:

set time_zone='America/New_York';
insert into SomeTable ( Name, Created, Modified ) VALUES ( 'Dude', '2009-06-15 11:54:00', '2009-06-15 11:54:00');
set time_zone='GMT';
SELECT * FROM SomeTable ORDER BY ID DESC LIMIT 1;
+----+------+---------------------+---------------------+
| ID | Name | Created             | Modified            |
+----+------+---------------------+---------------------+
|  1 | Dude | 2009-06-15 15:54:00 | 2009-06-15 15:54:00 | 
+----+------+---------------------+---------------------+

Just set the database time zone to the zone the user is in, and it will correctly "covert" it to the right GMT date.

Just make sure you do not "double convert" dates in both PHP and MySQL.