Using MySQL’s TIMESTAMP vs storing timestamps directly

by

Arguments for TIMESTAMP

  • It implicitly stores data in UTC time zone. No matter what your session time-zone is. Useful if you need to use different time zones.
  • You can have automated timestamping columns using DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP (one column per table only until MySQL 5.6.5)
  • You can use datetime function for date comparison, addition, subtraction, range lookup etc, without the need to use FROM_UNIXTIME() function – it will make it easier to write queries that can use indexes

  • In PHP 

    >> date('Y-m-d h:i:s',4294967295);
'1969-12-31 11:59:59'

    so the range is in fact the same

When UNIX_TIMESTAMP() is used on a TIMESTAMP column, the function
returns the internal timestamp value directly, with no implicit
“string-to-Unix-timestamp” conversion

More Related Contents:

  1. MySQL datetime fields and daylight savings time — how do I reference the “extra” hour?
  2. PHP DateTime supported formats
  3. timestamp within a form [closed]
  4. Display which day it is? sunday or monday “php coding”? [closed]
  5. Php date() Date and time is stuck [closed]
  6. Convert from MySQL datetime to another format with PHP
  7. Timezone conversion in php
  8. How to store repeating dates keeping in mind Daylight Savings Time
  9. Object of class DateTime could not be converted to string
  10. How to generate random date between two dates using php?
  11. How to store NULL values in datetime fields in MySQL?
  12. How to separate DATE and TIME from DATETIME in MySQL?
  13. PHP, MySQL and Time Zones
  14. Set timezone in PHP and MySQL
  15. NOW() function in PHP
  16. How to convert ISO8601 to Date format in php
  17. Date in a URL dd/mm/yyyy
  18. mysql – INSERT date range into date columns IF dates don’t overlap with existing ones
  19. adding 30 minutes to datetime php/mysql
  20. Formatting an SQL timestamp with PHP
  21. Storing time information: Timezone required?
  22. adding 1 day to a DATETIME format value
  23. How to convert MySQL time to UNIX timestamp using PHP?
  24. Subtracting a certain number of hours, days, months or years from date
  25. Im trying to create some temporary tables in mysql using php
  26. Convert latin1 characters on a UTF8 table into UTF8
  27. Using strtotime for dates before 1970
  28. How to compare two time in PHP
  29. MySQL GROUP BY and Fill Empty Rows
  30. Which MySQL datatype to use for an IP address? [duplicate]

Leave a Comment