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:
| 序号 | 时间字符串 | 实例 |
|---|---|---|
| 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 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