Timestamp with a millisecond precision: How to save them in MySQL

by

You need to be at MySQL version 5.6.4 or later to declare columns with fractional-second time datatypes. Not sure you have the right version? Try SELECT NOW(3). If you get an error, you don’t have the right version.

For example, DATETIME(3) will give you millisecond resolution in your timestamps, and TIMESTAMP(6) will give you microsecond resolution on a *nix-style timestamp.

Read this: https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html

NOW(3) will give you the present time from your MySQL server’s operating system with millisecond precision.

If you have a number of milliseconds since the Unix epoch, try this to get a DATETIME(3) value

FROM_UNIXTIME(ms * 0.001)

Javascript timestamps, for example, are represented in milliseconds since the Unix epoch.

(Notice that MySQL internal fractional arithmetic, like * 0.001, is always handled as IEEE754 double precision floating point, so it’s unlikely you’ll lose precision before the Sun becomes a white dwarf star.)

If you’re using an older version of MySQL and you need subsecond time precision, your best path is to upgrade. Anything else will force you into doing messy workarounds.

If, for some reason you can’t upgrade, you could consider using BIGINT or DOUBLE columns to store Javascript timestamps as if they were numbers. FROM_UNIXTIME(col * 0.001) will still work OK. If you need the current time to store in such a column, you could use UNIX_TIMESTAMP() * 1000

More Related Contents:

  1. Should I use the datetime or timestamp data type in MySQL?
  2. MySQL convert date string to Unix timestamp
  3. Group mysql query by 15 min intervals
  4. MySql difference between two timestamps in days?
  5. How do you set a default value for a MySQL Datetime column?
  6. MySQL Query GROUP BY day / month / year
  7. How to group by week in MySQL?
  8. Difference between two dates in MySQL
  9. MySQL Query to select data from last week?
  10. Mysql: Setup the format of DATETIME to ‘DD-MM-YYYY HH:MM:SS’ when creating a table
  11. Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT clause?
  12. How to store a datetime in MySQL with timezone info
  13. How to select date from datetime column?
  14. Datetime equal or greater than today in MySQL
  15. How to convert number of week into date?
  16. SQL Query to show nearest date?
  17. How to subtract hours from a datetime in MySQL?
  18. How to convert time to the time zone of the iPhone device?
  19. Select data between a date/time range
  20. mysql: get record count between two date-time
  21. How to select rows that have current day’s timestamp?
  22. How to cast DATETIME as a DATE in mysql?
  23. How to get current date & time in MySQL?
  24. Use MySQL to determine whether today is a user’s birthday
  25. How to compare timestamp dates with date-only parameter in MySQL?
  26. When is a timestamp (auto) updated?
  27. MySQL column type “TIMESTAMP” implicitly includes “NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”
  28. how to partition a table by datetime column?
  29. Get total hours worked in a day mysql
  30. MySQL – How to parse a string value to DATETIME format inside an INSERT statement?

Leave a Comment