SQLite Date & Time
SQLite supports the following five date and time functions:
| No. | 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 on November 24, 4714 BC Greenwich time. |
| 5 | strftime(format, timestring, modifier, modifier, ...) | This returns a formatted date according to the format string specified by the first parameter. 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 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 Strings
A time string can be in any of the following formats:
| No. | 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-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 separating the date and time.
Modifiers
The time string may be followed by zero or more modifiers that will alter the date and/or time returned by the above five functions. The above five functions all return time. Modifiers should be used from left to right. The following are the modifiers that can be used 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 functionstrftime()to format any date and time. You can use the following substitutions to format date and time:
| 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 | Second, 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. The following computes the current date:
sqlite> SELECT date('now');
2013-05-07
The following computes the last day of the current month:
sqlite> SELECT date('now','start of month','+1 month','-1 day');
2013-05-31
The following computes the date and time for the given UNIX timestamp 1092941466:
sqlite> SELECT datetime(1092941466, 'unixepoch'); 2004-08-19 18:51:06
The following computes the date and time for the given UNIX timestamp 1092941466 in the local time zone:
sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime'); 2004-08-19 11:51:06
The following computes the current UNIX timestamp:
sqlite> SELECT strftime('%s','now');
1367926057
The following computes the number of days since the signing of the U.S. "Declaration of Independence":
sqlite> SELECT julianday('now') - julianday('1776-07-04');
86504.4775830326
The following computes 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 computes 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
The following computes the time in seconds since the UNIX epoch (similar to strftime('%s','now'), except that it includes a fractional part):
sqlite> SELECT (julianday('now') - 2440587.5)*86400.0;
1367926077.12598
To convert between UTC and local time values, use the utc or localtime modifiers when formatting dates, as shown below:
sqlite> SELECT time('12:00', 'localtime');
05:00:00
sqlite> SELECT time('12:00', 'utc');
19:00:00
Other Extensions