0

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?

  • Maybe they wanted to be Y10K compliant? – Colin 't Hart Nov 26 '18 at 10:09
  • I wouldn't put it past them. – Traubenfuchs Nov 26 '18 at 10:21
  • Use something like DECIMAL(18,4)... It gives you a total of 18 digits, 4 of which after the decimal point (and 14 before the decimal point).. – CR241 Nov 27 '18 at 4:32
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.

0

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

  • Seems like DECIMAL(13,3) (8 bytes, 10 digits before the decimal point, 3 after) would suffice. For example, the current time is 1543423205.661. That's seconds since 1970. – Rick James Nov 28 '18 at 16:41
  • The schema owner is confident that the application will work until the heat death of the universe. – Traubenfuchs Nov 30 '18 at 8:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.