SQLite Date & Time Functions

SQLite supports five functions for working with dates and times. These are:

All of these functions accept a time string as an argument. They also accept other arguments for modifying/formatting the output.

The strftime() function is the most versatile function of the five. In fact, this function can do everything the other four can.

In other words, you could use strftime() for all your date and time formatting requirements. The other four functions are available solely for convenience.

Syntax

The syntax of each function is as follows:

date(timestring, modifier, modifier, ...)
time(timestring, modifier, modifier, ...)
datetime(timestring, modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)

In the first four functions, the first argument is a time string, and any subsequent arguments are modifiers.

In the strftime() function the first argument is the format string, followed by the time string, and any modifiers.

All valid time strings and modifiers are listed near the bottom of this article.

But first, let’s go through some quick examples of each of these functions.

The date() Function

You can use the date() function with one argument, or with multiple arguments.

Here’s an example with just one argument:

SELECT date('now');

Result:

2020-04-23

Here’s an example that uses a modifier:

SELECT date('now', 'localtime');

Result:

2020-04-24

In this case I used the localtime modifier. This modifier assumes that the time string is in Universal Coordinated Time (UTC) and adjusts the time string so that it displays local time.

The time() Function

The time() function is similar to the date() function, except that it outputs the time instead of the date.

Here’s an example:

SELECT time('now');

Result:

00:02:05

And here it is with the localtime modifier:

SELECT time('now', 'localtime');

Result:

10:02:09

The datetime() Function

The SQLite datetime() function combines the previous two functions, in the sense that it outputs both the date and the time.

Here’s an example:

SELECT datetime('now');

Result:

"2020-04-24 00:04:13"

And here it is with the localtime modifier:

SELECT datetime('now', 'localtime');

Result:

"2020-04-24 10:04:46"

The julianday() Function

The julianday() function returns the continuous count of days since the beginning of the Julian Period, which is Monday, January 1, 4713 BC, proleptic Julian calendar (November 24, 4714 BC, in the proleptic Gregorian calendar).

Here’s what it looks like using now as the timestring:

SELECT julianday('now');

Result:

2458963.50964815

And here it is using the localtime modifier:

SELECT julianday('now', 'localtime');

Result:

2458963.92637685

The strftime() Function

As mentioned, the strftime() function can be used to output date and time values in any of the formats available in the previous functions.

This function allows you to be very specific with how your date/time values are formatted. It enables you to provide the precise format that you want it to be presented.

The syntax of this function goes like this:

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

Notice that the first argument is format. This is where you provide the format that you’d like the time string to be output in.

The format string for strftime() can consist of any of the following substitutions:

%d	Day of month: 00
%f	Fractional seconds: SS.SSS
%H	Hour: 00-24
%j	Day of year: 001-366
%J	Julian day number
%m	Month: 01-12
%M	Minute: 00-59
%s	Seconds since 1970-01-01
%S	Seconds: 00-59
%w	Day of week 0-6 with Sunday==0
%W	Week of year: 00-53
%Y	Year: 0000-9999
%%	%

Here’s an example:

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

Result:

"2020-04-24 00:43:51"

In this case we get the same result as when using the datetime() function.

Here it is with the localtime modifier:

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

Result:

"2020-04-24 10:45:27"

Again, same result as with datetime(). The difference is, strftime() enables us to explicitly specify the format of the output.

For example:

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

Result:

"24/04/2020 10:49:41"
"04/24/2020 (10:52:24)"
"10:51:13 on 24/04/2020"

Valid Time String Formats

The time string argument in all date/time functions can be supplied in any of the following formats:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

Valid Modifiers

You can use any of the following modifiers with date/time functions:

NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of day
weekday N
unixepoch
localtime
utc

The N refers to a number that is added to the date, based on the unit specified.

For example:

SELECT 
  date('now'), 
  date('now', '3 days');

Result:

date('now')  date('now', '3 days')
-----------  ---------------------
2020-04-24   2020-04-27

Here’s one that uses start of month as the modifier:

SELECT 
  date('now'), 
  date('now', 'start of month');

Result:

date('now')  date('now', 'start of month')
-----------  -----------------------------
2020-04-24   2020-04-01