Below is a list of valid time string formats that you can use in any date and time function in SQLite.

The now time string returns the current date and time using Universal Coordinated Time (UTC).

The DDDDDDDDDD time string is the Julian day number expressed as a floating point value.

How do the Time Strings Work?

When using any of the SQLite date and time functions, you need to provide a time string. This time string represents the date/time that you’re trying to format or perform the operation on.

The time string you provide needs to adhere to one of the valid formats in the above table.

.mode line
SELECT 
  datetime('2020-12-30'),
  datetime('now'),
  datetime('2459213.94799769');

      datetime('2020-12-30') = 2020-12-30 00:00:00
             datetime('now') = 2020-04-24 22:58:31
datetime('2459213.94799769') = 2020-12-30 10:45:07

Timezone Indicator

All time strings that include the time (such as HH:MM, HH:MM:SS, etc) can also include a timezone indicator. This means all time strings from row 2 to row 10 in the above table.

The timezone indicator can take the form [+-]HH:MM or just Z.

The Z suffix doesn’t change anything, as the SQLite date and time functions already use UTC/zulu time internally anyway.

Any non-zero HH:MM suffix is subtracted from the indicated date and time in order to compute zulu time.

Example of Timezone Indicators

Here are some examples to demonstrate.

SELECT 
  datetime('10:45Z'),
  datetime('10:45+01:00'),
  datetime('10:45-01:00');

Result:

     datetime('10:45Z') = 2000-01-01 10:45:00
datetime('10:45+01:00') = 2000-01-01 09:45:00
datetime('10:45-01:00') = 2000-01-01 11:45:00