How to convert milliseconds to date in SQLite

In SQLite, date and time handling is typically performed using built-in date and time functions. If you have a value containing milliseconds (representing the number of milliseconds since the Unix epoch) and you want to convert it to a date format, you can use the datetime() function combined with appropriate conversion methods.

Here is a specific example to illustrate this:

Suppose we have a table named events which contains a column named timestamp_ms that stores milliseconds since the Unix epoch (January 1, 1970 UTC). Our goal is to convert these milliseconds into a human-readable date format.

First, we can use the following SQL command to perform this conversion:

sql
SELECT datetime(timestamp_ms / 1000, 'unixepoch') AS event_date FROM events;

Here's how it works:

• timestamp_ms / 1000: Since the datetime() function requires seconds, we need to convert milliseconds to seconds. This is achieved by simply dividing the milliseconds by 1000.
• datetime(..., 'unixepoch'): This part informs the datetime() function that the input seconds are since the Unix epoch. It converts this seconds value into a standard date and time format.

For example, if timestamp_ms is 1609459200000 (which represents the milliseconds for January 1, 2021 at 00:00:00 UTC), the above query will return:

shell
2021-01-01 00:00:00

Thus, we successfully convert the milliseconds stored in the SQLite database into a more understandable and usable date format. This conversion is very useful when handling time series data, log files, or any data requiring timestamp annotations.