datetime vs timestamp?

Question

What would you recommend using between a datetime and a timestamp field, and why? (using mysql). I'm working with php on the server side.

Answer 1

I would always use a unix timestamp when working with MySQL and PHP. The main reason for this being the the default date method in php uses a timestamp as the parameter so there would be no parsing needed.

To get the current unix timestamp in php just do time(); and in MySQL do SELECT UNIX_TIMESTAMP();

Answer 2

A timestamp field is a special case of the datetime field. You can create timestamp columns to have special properties; it can be set to update itself on either create and/or update.

In "bigger" database terms, tiemstamp has a couple of special-case triggers on it.

What the right one is depends entirely on what you want to do.

Answer 3

Timestamps in MySQL generally used to track changes to records, and are 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.

Answer 4

I make this decision on a semantical base.

I use a timestamp when I need to record a (more or less) fixed point in time. For example when a record was inserted into the database or when some useraction took place.

I use a datetime field when the date/time can be set and changed arbitrarily. For example when a user can save later change appointments.

Answer 5

I always use DATETIME fields for anything other than row metadata (date created or modified).

As mentioned in the MySQL documentation:

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

...

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.

You're quite likely to hit the lower limit on TIMESTAMPs in general use -- e.g. storing birthdays.

Answer 6

I prefer using timestamp so to keep everything in one common raw format and format the data in PHP code or in your SQL query. There are instances where it comes in handy in your code to keep everything in plain seconds.

Answer 7

TIMESTAMP is 4 bytes Vs 8 bytes for DATETIME.

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

But like scronide said it does have a lower limit of the year 1970. It's great for anything that might happen in the future though ;)

Answer 8

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

Answer 9

timestamp is slower than datetime so i prefered datetime datatime in mysql

www.bageshsingh.com

Answer 10

The main difference is that datetime is constant while timestimap is effected by the time_zone setting.

So it only matters when you have - or may in the future have - syncronized clusters accross timezones.

In simpler words: If I have a database in Australia, and take a dump of that database to syncronize/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 timezone.

A great example of datetime being used where timestamp should have been used is in facebook, where theyre servers are never quite sure what time stuff happened accross timezones. Once I was haing a conversation in which the time said I was replying to messages before they message was actually sent :) This of course could also have been caused by bad timezone translation in the messaging software if the times were being posted rather than syncronized.

I hope that makes some sense coz Im tired.

Regards, Eugene.

Answer 11

TIMESTAMP is always in UTC (i.e. elapsed seconds since 1970-01-01, in UTC), and your mySQL server auto-converts it to the date/time for the server timezone. In the long-term, TIMESTAMP is the way to go b/c you know your temporal data will always be in UTC. E.G. you won't screw your dates up if you migrate to a different server or if you change the timezone settings on your server.

Answer 12

Depends on application, really.

Consider setting a timestamp by a user to a server in New York, for an appointment in Sanghai. Now when the user connects in Sanghai, he accesses the same appointment timestamp from a mirrored server in Tokyo. He will see the appointment in Tokyo time, offset from the original New York time.

So for values that represent user time like an appointment or a schedule, datetime is better. It allows the user to control the exact date and time desired, regardless of the server settings. The set time is the set time, not affected by the server's time zone, the user's time zone, or by changes in the way daylight savings time is calculated (yes it does change).

On the other hand, for values that represent system time like payment transactions, table modifications or logging, always use timestamps. The system will not be affected by moving the server to another time zone, or when comparing between servers in different timezones.

Timestamps are also lighter on the database and indexed faster.