3

I'm new in MySQL database.

Would you recommend using a datetime or a timestamp field in table creation and why. I'm using MySQL 5.7 and innodb engine.

Thanks

Damien
  • 95
  • 1
  • 9
  • 1
    datetime field only contains date year month while timestamp fileld contains hour minute second also... So its depend on your requirement and how's you data storing....but i would recommen to take timestamp fileld that will be easy to filter and order the data – Ankit Agrawal Mar 17 '17 at 08:17
  • 4
    @AnkitAgrawal : dateTIME does not only containt "date year month". It also contains time. Hence the name... – Nanne Mar 17 '17 at 08:19
  • You can also see http://stackoverflow.com/questions/409286/should-i-use-field-datetime-or-timestamp – Oto Shavadze Mar 17 '17 at 08:23
  • Possible duplicate of [Should I use field 'datetime' or 'timestamp'?](http://stackoverflow.com/questions/409286/should-i-use-field-datetime-or-timestamp) – Blue Apr 12 '17 at 01:19

3 Answers3

3

I would use TIMESTAMP for anything that needs to be managed automatically since it supports stuff like ON UPDATE CURRENT_TIMESTAMP or having CURRENT_TIMESTAMP as the default value.

This isn't possible with DATETIME but I do like the format better since you don't need to convert it. So I'd use that for everything else.

If you need a flexible format that can be autogenerated, then you'd probably have to go with TIMESTAMP and convert when needed.

Chris
  • 57,622
  • 19
  • 111
  • 137
2

For a creation field I would use a timestamp. You will not get into trouble with timezone stuff this way, which can be pretty tricky.

Timestamp is not usefull for, for instance, birthdays, as you have to deal with the EPOCH aka timestamp '0', which is 1 january 1970. But for creation-time it should not matter.

Nanne
  • 64,065
  • 16
  • 119
  • 163
-5

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

So it is most advisable to use datetime for most database operations

Douglas Hosea
  • 1,002
  • 13
  • 30