Comments on: TIMESTAMP vs. DATETIME, which should I be using?

By: Shlomi Noach

Shlomi Noach — Tue, 01 May 2018 05:38:00 +0000

> I did a little research and it seems that best practice would be to set all your severs in UTC timezone and use TIMESTAMP datatype for dates.

That’s a good rule to work with.

By: SacTiw

SacTiw — Mon, 30 Apr 2018 13:19:00 +0000

So “which-should-i-be-using” that part is not addressed or did I miss it?

Moreover, I find the difference to be more vague since, as of MySQL 5.6.5, both TIMESTAMP and DATETIME columns can be automatically initialized and updated to the current date and time.

Also, normally when people talk about timezone being changed, are they referring to timezone of server itself (why would that happen?) or its replica located in different timezone or are they talking about end-user sitting in some other timezone and viewing the data (how should that matter as MySQL is not considering that while storing/retrieval)?

I did a little research and it seems that best practice would be to set all your severs in UTC timezone and use TIMESTAMP datatype for dates. Of course, year 2038 limit of TIMESTAMP is a concern but hopefully we will fix it well in time 🙂

What are your thoughts?

By: Ovais Tariq

Ovais Tariq — Fri, 03 Jun 2011 10:51:25 +0000

There are other major differences between TIMESTAMP and DATETIME,

1. TIMESTAMP columns can be told to be updated on INSERTs and UPDATEs, without providing a value for these columns, that is particularly useful in logging.
2. TIMESTAMP columns are converted back and forth between UTC and the session timezones, when the value is inserted/updated in the TIMESTAMP column its converted to UTC, and when the value is retrieved its converted to your session timezone

By: Ziv

Ziv — Fri, 27 May 2011 16:19:16 +0000

@Shlomi,
Usually I’m not taking the unix time stamp from my code, but using thd DB function that fetch its (the DB servers) time.

By: shlomi

shlomi — Thu, 26 May 2011 14:42:13 +0000

@Ziv,
But then you lose timestamp information; or are you always using UTC?
Another thing: using the DB’s timestamps ensures consistency. For example, imagine two machines not in sync with global time, which have time offset between themselves.
Also, as a more common scenario. machines that do not always correctly update with the latest Summer Saving time info (especially true for Israel, where the date is not constant and must be updated each year).

By: Ziv

Ziv — Thu, 26 May 2011 14:11:05 +0000

I always prefer using unix time stamp rather than using the DB date, datetime or timestamp types.

Using unix time stamp I gain portability to other system, and – by using very simple arithmetic in the SQL I have all the functions available for dates and time…

By: Shantanu Oak

Shantanu Oak — Tue, 24 May 2011 07:42:21 +0000

While dumping data, –tz-utc needs to be used …

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_tz-utc
_____

@Chris
>> MySQL’s magic auto-update-on-modification behavior has led me to avoid timestamp

agreed. very true.

>> store things as INTs. FROM_UNIXTIME()
I have seen people doing this, but I prefer good old datetime!

By: Baron Schwartz

Baron Schwartz — Sun, 22 May 2011 11:33:53 +0000

Remember the bad old days, when TIMESTAMP and DATETIME were formatted differently when displayed to the user?

Date and time types are one of the places where MySQL shows a bit of a legacy. I know the Drizzle developers have a lot to say about this, too 🙂

By: Chris

Chris — Sun, 22 May 2011 05:29:30 +0000

I’ve always found it odd that a DATETIME is actually two bytes larger than storing DATE and TIME columns separately. TIMESTAMP’s history of magic auto-update-on-modification behavior has led me to avoid them in order to prevent confusion among developers less familiar with MySQL’s magic (at least you can disable it now, but that wasn’t always the case).

Anyway, I generally just store things as INTs. FROM_UNIXTIME() exists for those few times I want a date string out of the database instead of something easy (like an INT) to feed into my language’s date/time class.