How to convert Unix timestamp to Datetime in SQLite

Code Explanation

In SQLite it is possible to use the datetime() or date() functions combined with the unixepoch modifier to convert a Unix timestamp.
The unixepoch modifier instructs SQLite to interpret the given value as a Unix timestamp. The datetime() function returns the result in the format YYYY-MM-DD HH:MM:SS, including both the date and time, while the date() function returns only the date in the format YYYY-MM-DD. In this example UnixTimestamp holds the value 1621444631 and is converted to 2021-05-19 17:17:11.

Other useful SQLite date functions

Function	Description	Example
date()	Returns the date in the format 'YYYY-MM-DD'. Can be used with or without modifiers like 'unixepoch'.	date(UnixTimestamp, 'unixepoch') returns 2024-08-28
datetime()	Returns the date and time in the format 'YYYY-MM-DD HH:MM:SS'. Can also be used with the 'unixepoch' modifier.	datetime(UnixTimestamp, 'unixepoch') returns 2024-08-28 12:34:56
strftime()	Formats the date and time according to a specified format string. Can be used with 'unixepoch' to convert Unix timestamps.	strftime('%Y-%m-%d %H:%M:%S', UnixTimestamp, 'unixepoch') returns 2024-08-28 12:34:56
julianday()	Returns the Julian day number which is the number of days since noon in Greenwich on November 24, 4714 B.C. Can be used with 'unixepoch'.	julianday(UnixTimestamp, 'unixepoch') returns 2460150.02315
time()	Returns the time in 'HH:MM:SS' format. Useful for extracting time from a timestamp.	time(UnixTimestamp, 'unixepoch') returns 12:34:56
current_date	Returns the current date as 'YYYY-MM-DD'.	current_date returns 2024-08-28
current_time	Returns the current time as 'HH:MM:SS'.	current_time returns 12:34:56
current_timestamp	Returns the current date and time as 'YYYY-MM-DD HH:MM:SS'.	current_timestamp returns 2024-08-28 12:34:56