The SQLite strftime() function enables you to return a date and time value in a specified format.

The actual date/time value returned is based on a time string that you provide as an argument when you call the function, as well as any modifiers you include (as optional arguments). A modifier allows you to change the date, such as add a number of days, set it to local time, etc

Syntax

The syntax goes like this:

strftime(format, timestring, modifier, modifier, ...)

The format argument can be any combination of valid format string substitutions for the strftime() function.

The timestring argument must be a valid time string.

The modifier arguments are optional. You can provide one or more modifiers. If you provide a modifier, it must be a valid modifier.

Example

Here’s an example to demonstrate.

SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');

Result:

2020-04-29 01:29:54

The now time string is converted into the current date and time, based on the format string provided.

In this case, my format string returns exactly the same result that datetime('now') would have returned.

Add a Modifier

We can modify the previous result by using a modifier. Here’s an example.

SELECT strftime('%Y-%m-%d %H:%M:%S', 'now', '+2 hours');

Result:

2020-04-29 03:31:50

In this case I added two hours to the time.

Multiple Modifiers

You can add multiple modifiers. Here’s an example of adding another modifier to the previous example.

SELECT strftime('%Y-%m-%d %H:%M:%S', 'now', '+2 hours', 'localtime');

Result:

2020-04-29 13:33:22

In my case, the localtime modifier resulted in the time being moved forward.

This is because the localtime modifier assumes the time string provided is in Universal Coordinated Time (UTC). It then adjusts the time string so that it displays local time.

You may get a different result, depending on your local time.

Here it is again comparing the original local time with the modified result:

SELECT 
  strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime') AS "Local",
  strftime('%Y-%m-%d %H:%M:%S', 'now', '+2 hours', 'localtime') AS "Modified";

Result:

Local                Modified           
-------------------  -------------------
2020-04-29 11:34:57  2020-04-29 13:34:57

Unix/Epoch Time

You can use the %s format string (lowercase) to calculate epoch time.

SELECT strftime('%s', 'now');

Result:

1588124826

Epoch time (also known as Epoch time, POSIX time, seconds since the Epoch, or UNIX Epoch time) is the number of seconds since 1970-01-01.

Unix time is widely used in operating systems and file formats.

Julian Day

You can use %J to return the Julian Day.

Julian Day is the number of days since noon in Greenwich on November 24, 4714 B.C. (using the Proleptic Gregorian calendar).

If using the proleptic Julian calendar, this is Monday, January 1, 4713 BC.

Julian day is typically used by astronomers, software, etc to calculate elapsed days between two events.

Here’s how to return the Julian Day with the strftime() function.

SELECT strftime('%J', 'now');

Result:

2458968.575441667               

Another way of doing this in SQLite is to use the julianday() function.

Date Range

As with all SQLite date and time functions, strftime() only works for dates between 0000-01-01 00:00:00 and 9999-12-31 23:59:59 (julian day numbers 1721059.5 through 5373484.5).

For dates outside that range, the results are undefined.