How to store date & time in DECIMAL(18,3)?

Question

I am currently working with a database scheme that contains a DECIMAL(18,3) column that is supposed to contain date and time. Sadly, I do not have any example data and need to create it myself.

However, I do not know how to correctly format the dates I want to insert, because when you add together year, month, day, hours, minutes and seconds, you get 14, if you add 3 more numbers for the milliseconds you get 17. Where is the eighteenth number hiding from me?

Answer 1

MySQL has two date+time formats:

For 1-second resolution, use the datatype DATETIME or TIMESTAMP:

mysql> SELECT NOW() AS sample_DATETIME, UNIX_TIMESTAMP() AS sample_TIMESTAMP;
+---------------------+------------------+
| sample_DATETIME     | sample_TIMESTAMP |
+---------------------+------------------+
| 2018-11-26 12:45:15 |       1543265115 |
+---------------------+------------------+

To include milliseconds, use DATETIME(3) or TIMESTAMP(3):

mysql> SELECT NOW(3) AS sample_DATETIME;
+-------------------------+
| sample_DATETIME         |
+-------------------------+
| 2018-11-26 12:46:35.093 |
+-------------------------+

Think of DATETIME as a picture of the clock. Note that it has suitable hiccups when Daylight Savings comes or goes.

TIMESTAMP is essentially UTC, but converted from/to your timezone.

I have not heard of anyone using DECIMAL.

Answer 2

I received an answer from the schema owners: Apparently it's Unix time.