YouTip

SQLite supports the following five date and time functions:

Number	Function	Example
1	date(timestring, modifier, modifier, ...)	Returns the date in YYYY-MM-DD format.
2	time(timestring, modifier, modifier, ...)	Returns the time in HH:MM:SS format.
3	datetime(timestring, modifier, modifier, ...)	Returns in YYYY-MM-DD HH:MM:SS format.
4	julianday(timestring, modifier, modifier, ...)	This returns the number of days since noon in Greenwich on November 24, 4714 BC.
5	strftime(format, timestring, modifier, modifier, ...)	This returns a formatted date based on the format string specified in the first argument. See the explanation below for specific formats.

The above five date and time functions take a time string as an argument. The time string is followed by zero or more modifier modifiers. The strftime() function can also take a format string as its first argument. Below, we will explain in detail the different types of time strings and modifiers.

Time String

A time string can be in any of the following formats:

Number	Time String	Example
1	YYYY-MM-DD	2010-12-30
2	YYYY-MM-DD HH:MM	2010-12-30 12:10
3	YYYY-MM-DD HH:MM:SS.SSS	2010-12-30 12:10:04.100
4	MM-DD-YYYY HH:MM	12-30-2010 12:10
5	HH:MM	12:10
6	YYYY-MM-DD**T**HH:MM	2010-12-30 12:10
7	HH:MM:SS	12:10:01
8	YYYYMMDD HHMMSS	20101230 121001
9	now	2013-05-07

You can use "T" as a literal character to separate date and time.

Modifiers (Modifier)

A time string can be followed by zero or more modifiers that alter the date and/or time returned by the five functions above. Any of the five functions can return time. Modifiers should be used from left to right. The following modifiers are available for use in SQLite:

• 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

Formatting

SQLite provides a very convenient function strftime() to format any date and time. You can use the following substitutions to format dates and times:

Substitution	Description
%d	Day of the month, 01-31
%f	Seconds with fractional part, SS.SSS
%H	Hour, 00-23
%j	Day of the year, 001-366
%J	Julian day number, DDDD.DDDD
%m	Month, 00-12
%M	Minute, 00-59
%s	Seconds since 1970-01-01
%S	Seconds, 00-59
%w	Day of the week, 0-6 (0 is Sunday)
%W	Week of the year, 01-53
%Y	Year, YYYY
%%	% symbol

Examples

Now let's try different examples using the SQLite prompt. Here is calculating the current date:

sqlite> SELECT date('now');
2013-05-07

Here is calculating the last day of the current month:

sqlite> SELECT date('now','start of month','+1 month','-1 day');
2013-05-31

Here is calculating the date and time for a given UNIX timestamp 1092941466:

sqlite> SELECT datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06

Here is calculating the date and time for a given UNIX timestamp 1092941466 relative to the local time zone:

sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
2004-08-19 11:51:06

Here is calculating the current UNIX timestamp:

sqlite> SELECT strftime('%s','now');
1367926057

Here is calculating the number of days since the signing of the American "Declaration of Independence":

sqlite> SELECT julianday('now') - julianday('1776-07-04');
86504.4775830326

Here is calculating the number of seconds since a specific moment in 2004:

sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
295001572

Here is calculating the date of the first Tuesday in October of the current year:

sqlite> SELECT date('now','start of year','+9 months','weekday 2');
2013-10-01

Here is calculating the time in seconds since the UNIX epoch (similar to strftime('%s','now'), but includes the fractional part):

sqlite> SELECT (julianday('now') - 2440587.5)*86400.0;
1367926077.12598

To convert between UTC and local time values when formatting dates, use the utc or localtime modifiers, as shown below:

sqlite> SELECT time('12:00', 'localtime');
05:00:00

sqlite> SELECT time('12:00', 'utc');
19:00:00