SQLite Date & Time

SQLite supports the following five date and time functions:

Serial number	Function	Example
1	date(timestring, modifier, modifier, ...)	Returns the date in YYYY-MM-DD format.
2	time(timestring, modifier, modifier, ...)	Return the time in HH:MM:SS format.
3	datetime(timestring, modifier, modifier, ...)	Return in YYYY-MM-DD HH:MM:SS format .
4	julianday(timestring, modifier, modifier, ...)	This will return November 4714 BC from GMT The number of days from noon on the 24th.
5	strftime(format, timestring, modifier, modifier, ...)	This will be based on the format character specified by the first parameter String returns the formatted date. The specific format is explained below.

The above five date and time functions take time strings as parameters. A time string followed by zero or more modifier modifiers. The strftime() function can also take the format string format as its first argument. The different types of time strings and modifiers will be explained to you in detail below.

Time string

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

Serial number	Time String	Instance
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	30-12-2010 12:10
5	HH:MM	12:10
6	YYYY-MM-DDTHH: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 the literal character that separates date and time.

Modifier

The time string can be followed by zero or more modifiers, which will change the date and/or time returned by the above five functions. Any of the above five functions return time. Modifiers should be used from left to right, and the modifiers that can be used in SQLite are listed below:

• 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 format dates and times using the following substitutions:

ReplacementDescription%dThe day of the month, 01-31%fSeconds with decimal part, SS.SSS%HHour, 00-23%jDay of the year, 001-366%JJulian day number, DDDD.DDDD%mmonth, 00-12 %M points, 00-59##%s%S%w%W%Y%%Examples

from 1970-01- Number of seconds since 01
seconds, 00-59
The number of days in the week, 0-6 (0 is Sunday)
The number of weeks in the year, 01-53
year, YYYY
% symbol

Now let's try a different instance using the SQLite prompt. The following is to calculate the current date:

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

The following is to calculate the last day of the current month:

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

The following is to calculate the date sum given the UNIX timestamp 1092941466 Time:

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

The following is the calculation of the date and time relative to the local time zone for the given UNIX timestamp 1092941466:

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

The following is the calculation of the current UNIX timestamp:

sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
1367926057

The following is to calculate the number of days since the signing of the United States "Declaration of Independence":

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

The following is to calculate the number of seconds since a specific moment in 2004:

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

The following is to calculate the first day of October of that year Tuesday's date:

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

The following is a calculation of the time in seconds since the UNIX epoch (similar to strftime('%s','now'), except that the decimal part is included) :

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

Convert between UTC and local time values, when formatting dates, use the utc or localtime modifier as follows:

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

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