1.45. 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, ...)`	Returns the time in HH:MM:SS format.
3	`datetime(timestring, modifier, modifier, ...)`	Returned in YYYY-MM-DD HH:MM:SS format.
4	`julianday(timestring, modifier, modifier, ...)`	This returns the number of days from noon on November 24, 4714 B.C., GMT.
5	`strftime(format, timestring, modifier, modifier, ...)`	This returns the formatted date based on the format string specified by the first parameter. The specific format is explained below.

The above five date and time functions take a time string as an argument. The time string is followed by zero or more modifier Modifier. strftime() Function can also change the format string format As its first parameter. The different types of time strings and modifiers are explained in detail below.

1.45.1. Time string ¶

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

Serial 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	10:04.1
4	MM-DD-YYYY HH:MM	12-30-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-5-7

You can use “T” as a text character that separates date and time.

1.45.2. Modifier (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

1.45.3. Formatting ¶

SQLite provides very convenient functions strftime() To format any date and time. You can format the date and time using the following replacements:

Replace	Description
% d	The day of the month, 01-31
% f	带小数部分的秒，SS.SSS
% H	Hours, 00-23
% j	The day of the year, 001-366
% J	儒略日数，DDDD.DDDD
% m	Month, 00-12
% M	Score, 00-59
% s	Seconds from 1970-01-01
% S	Seconds, 00-59
% w	Day of the week, 0-6 (0 is Sunday)
% W	The week of the year, 01-53
% Y	Year, YYYY
%%	% symbol

1.45.4. Example ¶

Now let’s try a different instance using the SQLite prompt. The following is the current date calculated:

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

The following is the last day of the current month:

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

The following is the date and time at which a given UNIX timestamp 1092941466 is calculated:

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

The following is the date and time calculated 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 the calculation of the current UNIX timestamp:

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

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

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

The following is the number of seconds since a particular time in 2004:

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

The following is the date on which the first Tuesday of October of the year is calculated:

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

The following is the calculation of the time in seconds from the UNIX era (similar to strftime), except that the decimal part is included:

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

Convert between the UTC and the local time value, using the utc or localtime modifier when formatting the date, as follows:

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

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