Home > Questions > datetime vs timestamp?

datetime vs timestamp?

Answered
1
164

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.

NOTE: This question was originally posted at StackOverflow.com by m_oLogin

  • You must to post comments
Good Answer
283
0

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.

NOTE: This answer was originally posted at StackOverflow.com by blivet

  • Theresa
    Also 1 important note, DATETIME and TIMESTAMP can use CURRENT_TIMESTAMP, NOW() respectfully as it's default value, but DATE for example can't, because it uses '0000-00-00' by default, so to solve that matter U should write Your own trigger to that table, to insert current date (without time) in the field/col with DATE mysql type.

    NOTE: This comment was originally posted at StackOverflow.com by zeusakm

  • Randall
    in response to @zeusakm, there is a limitation that only one timestamp column per table can have default current_timestamp setting.

    NOTE: This comment was originally posted at StackOverflow.com by restart

  • Theresa
    Yeap this is a pertinent remark and this feature logically fair, there is no need of others.

    NOTE: This comment was originally posted at StackOverflow.com by zeusakm

  • You must to post comments
100
0

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 birthdate.

NOTE: This answer was originally posted at StackOverflow.com by scronide

  • Jimmy
    Thank you for answer, especially for the link to official documentation.

    NOTE: This comment was originally posted at StackOverflow.com by furikuretsu

  • Douglas
    you can also hit the upper limit easily if you are in banking or real estate... 30-year mortgages go beyond 2038 now

    NOTE: This comment was originally posted at StackOverflow.com by Kip

  • You must to post comments
49
0

The main difference is that DATETIME is constant while TIMESTAMP is effected by the time_zone setting.

So it only matters when you have – or may in the future have – synchronized clusters across time zones.

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

A great example of DATETIME being used where TIMESTAMP should have been used is in Facebook, where their servers are never quite sure what time stuff happened across time zones.

Once I was having a conversation in which the time said I was replying to messages before the message was actually sent.

This of course could also have been caused by bad time zone translation in the messaging software if the times were being posted rather than synchronized.

NOTE: This answer was originally posted at StackOverflow.com by ekerner

  • You must to post comments
33
0
mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+
2 rows in set (0.00 sec)

mysql> create table datedemo(
    -> mydatetime datetime,
    -> mytimestamp timestamp
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into datedemo values ((now()),(now()));
Query OK, 1 row affected (0.02 sec)

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone="america/new_york";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+
1 row in set (0.00 sec)

The above examples shows that how TIMESTAMP date type changed the values after changing the time-zone to 'america/new_work' where DATETIMEis unchanged.

I’ve converted my answer into article so more people can find this useful.

http://www.tech-recipes.com/rx/22599/mysql-datetime-vs-timestamp-data-type/

NOTE: This answer was originally posted at StackOverflow.com by Viswanathan Iyer

  • You must to post comments
31
0

I make this decision on a semantic 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 user action 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.

NOTE: This answer was originally posted at StackOverflow.com by unbeknown

  • demo
    Adada s
  • You must to post comments
11
0

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.

NOTE: This answer was originally posted at StackOverflow.com by ianaré

  • You must to post comments
1
0

From my experiences If you want a date field in which insertion happens only once and u don’t want o have update or any other action on that particular field go with date time .

For example in a user table REGISTRATION DATE filed.

In that user table if u want to know the last logged in time of a particular user go with a filed of timestamp type let that filed updated with a trigger.

NOTE: This answer was originally posted at StackOverflow.com by Kannan Prasad

  • You must to post comments
1
0

Not sure if this has been mentioned already, but worth noting in MySQL you can use something along the lines of below when creating your table columns

on update CURRENT_TIMESTAMP

This will update the time each instance you modify a row, sometimes very helpful for stored last edit info. This only works with timestamp, not datetime however.

NOTE: This answer was originally posted at StackOverflow.com by leejmurphy

  • You must to post comments
1
0

I always use a UNIX timestamp, simply to maintain sanity when dealing with a lot of datetime info, especially when performing adjustments for timezones, adding/subtracting dates, and the like. When comparing timestamps, this excludes the complicating factors of timezone and allows you to spare resources in your server side processing (Whether it be application code or database queries) in that you make use of light weight arithmetic rather then heavier date-time add/subtract functions.

NOTE: This answer was originally posted at StackOverflow.com by Oliver Holmberg

  • You must to post comments
0
0

I’m not sure it it’s already been answered, but I found unsurpassed usefulness in TIMESTAMP’s ability to auto update itself based on the current time without the use of unnecessary triggers. That’s just me though, although TIMESTAMP is UTC like it was said, it can keep track across different timezones, so if you need to display a relative time for instance, UTC time is what you would want.

NOTE: This answer was originally posted at StackOverflow.com by Marc DiMillo

  • You must to post comments
-2
0

I like UNIX timestamp, because you can convert to numbers and just worry about the number. Plus you add/subtract and get durations etc. Then convert the result to Date in whatever format. This code finds out how much time in minutes passed between a timestamp from a document, and the current time.

$date  = $item['pubdate']; (etc ...)
$unix_now = time();
$result = strtotime($date, $unix_now);  
$unix_diff_min = (($unix_now  - $result) / 60);
$min = round($unix_diff_min);

NOTE: This answer was originally posted at StackOverflow.com by user723220

  • You must to post comments
0
0

> adfadsfasdfasdf

asdfasdfasdf

jj

  • You must to post comments
Showing 12 results
Your Answer
Guest Author
Post as a guest by filling out the fields below or if you already have an account.
Name*
E-mail*
Website
CAPTCHA*
Enter the characters shown on the image.