If you have a Unix timestamp, you can use SQLite’s DATETIME() function with the unixepoch modifier to compute the actual date and time.

You can alternatively use the DATE() function if you only need the date to be returned. And it’s also possible to use the TIME() function to return just the time portion.

Get the Date and Time

Here’s an example of using the DATETIME() function with the unixepoch modifier:

SELECT DATETIME(1793956207, 'unixepoch');

Result:

2026-11-06 09:10:07

Get the Date

We can use the DATE() function if we only need the date to be returned:

SELECT DATE(1793956207, 'unixepoch');

Result:

2026-11-06

Get the Time

Using the TIME() function returns just the time portion:

SELECT TIME(1793956207, 'unixepoch');

Result:

09:10:07

Compensate for your Local Timezone

You can also add the localtime modifier to adjust the output to your local timezone:

SELECT DATETIME(1793956207, 'unixepoch', 'localtime');

Result:

2026-11-06 19:10:07

The localtime modifier assumes that the specified time value is in Universal Coordinated Time (UTC) and adjusts that time value so that it is in localtime.

The auto Modifier

From SQLite 3.38.0, we can use the auto modifier in place of the unixepoch modifier:

SELECT DATETIME(1793956207, 'auto');

Result:

2026-11-06 09:10:07

The auto modifier causes the value to be interpreted as either a Julian day number or a Unix timestamp, depending on the actual value.

If the value is between 0.0 and 5373484.499999, then it is interpreted as a Julian day number (corresponding to dates between -4713-11-24 12:00:00 and 9999-12-31 23:59:59, inclusive). For numeric values outside of the range of valid Julian day numbers, but within the range of -210866760000 to 253402300799, the auto modifier causes the value to be interpreted as a Unix timestamp. Other numeric values are out of range and cause a NULL return.