Latest web development tutorials

SQLite Date & Time

SQLite supports the following five date and time functions:

No. function Examples
1 date (timestring, modifier, modifier, ...) Returns the date format to YYYY-MM-DD.
2 time (timestring, modifier, modifier, ...) In HH: MM: SS format return time.
3 datetime (timestring, modifier, modifier, ...) In YYYY-MM-DD HH: MM: SS format to return.
4 julianday (timestring, modifier, modifier, ...) This returns the number of days from the time of 4714 BC Greenwich on November 24 at noon counting.
5 strftime (format, timestring, modifier, modifier, ...) This returns the date formatted according to the first parameter specifies the format string. See below explain specific format.

The five date and time functions the time string as a parameter. Time string followed by zero or more modifier modifiers. strftime () function can also format string format as its first argument. Below you will find a detailed explanation of different types of time strings and modifiers.

Time string

A time string can be any of the following formats:

序号时间字符串实例
1YYYY-MM-DD2010-12-30
2YYYY-MM-DD HH:MM2010-12-30 12:10
3YYYY-MM-DD HH:MM:SS.SSS2010-12-30 12:10:04.100
4MM-DD-YYYY HH:MM30-12-2010 12:10
5HH:MM12:10
6YYYY-MM-DDTHH:MM2010-12-30 12:10
7HH:MM:SS12:10:01
8YYYYMMDD HHMMSS20101230 121001
9now2013-05-07

You can use the "T" separating the date and time as text characters.

Modifiers (Modifier)

Time string can be followed back zero or more modifiers that will change the date of the five function returns and / or time. Any of the above five functions return time. From left to right modifier should be used modifiers are listed below can be used in the 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

format

SQLite provides a very convenient functionstrftime () to format any date and time.You can use the following format to replace the date and time:

替换描述
%d一月中的第几天,01-31
%f带小数部分的秒,SS.SSS
%H小时,00-23
%j一年中的第几天,001-366
%J儒略日数,DDDD.DDDD
%m月,00-12
%M分,00-59
%s从 1970-01-01 算起的秒数
%S秒,00-59
%w一周中的第几天,0-6 (0 is Sunday)
%W一年中的第几周,01-53
%Y年,YYYY
%%% symbol

Examples

Now let's use SQLite prompt try different instances. The following is a calculation of the current date:

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

The following is a calculation of the last day of the current month:

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

The following is a calculation of a given UNIX timestamp of the date and time 1092941466:

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

The following is a calculation of a given date and time UNIX timestamp 1092941466 relatively local time zone:

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

The following is a calculation of the current UNIX timestamp:

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

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

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

The following is calculated since 2004, at a given moment the number of seconds:

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

The following is a calculation of the first Tuesday of October of that year's date:

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

The following is calculated from the UNIX epoch in seconds counting time (similar to the strftime ( '% s', 'now'), is that there are different including fractional parts):

sqlite> SELECT (julianday ( 'now') - 2440587.5) * 86400.0;
1,367,926,077.12598

In between UTC and local time, the value of the conversion, when the date formatting, use utc or localtime modifier, as follows:

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