”;
SQLite supports five date and time functions as follows −
| Sr.No. | Function | Example |
|---|---|---|
| 1 | date(timestring, modifiers…) | This returns the date in this format: YYYY-MM-DD |
| 2 | time(timestring, modifiers…) | This returns the time as HH:MM:SS |
| 3 | datetime(timestring, modifiers…) | This returns YYYY-MM-DD HH:MM:SS |
| 4 | julianday(timestring, modifiers…) | This returns the number of days since noon in Greenwich on November 24, 4714 B.C. |
| 5 | strftime(timestring, modifiers…) | This returns the date formatted according to the format string specified as the first argument formatted as per formatters explained below. |
All 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 also takes a format string as its first argument. Following section will give you detail on different types of time strings and modifiers.
Time Strings
A time string can be in any of the following formats −
| Sr.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 | 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” as a literal character separating the date and the time.
Modifiers
The time string can be followed by zero or more modifiers that will alter date and/or time returned by any of the above five functions. Modifiers are applied from the left to right.
Following modifers are available 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
Formatters
SQLite provides a very handy function strftime() to format any date and time. You can use the following substitutions to format your date and time.
| Substitution | Description | %d | Day of month, 01-31 | %f | Fractional seconds, SS.SSS | %H | Hour, 00-23 | %j | Day of year, 001-366 | %J | Julian day number, DDDD.DDDD | %m | Month, 00-12 | %M | Minute, 00-59 | %s | Seconds since 1970-01-01 | %S | Seconds, 00-59 | %w | Day of week, 0-6 (0 is Sunday) | %W | Week of year, 01-53 | %Y | Year, YYYY | %% | % symbol |
|---|
Examples
Let”s try various examples now using SQLite prompt. Following command computes the current date.
sqlite> SELECT date(''now'');
2013-05-07
Following command computes the last day of the current month.
sqlite> SELECT date(''now'',''start of month'',''+1 month'',''-1 day'');
2013-05-31
Following command computes the date and time for a given UNIX timestamp 1092941466.
sqlite> SELECT datetime(1092941466, ''unixepoch''); 2004-08-19 18:51:06
Following command computes the date and time for a given UNIX timestamp 1092941466 and compensate for your local timezone.
sqlite> SELECT datetime(1092941466, ''unixepoch'', ''localtime''); 2004-08-19 13:51:06
Following command computes the current UNIX timestamp.
sqlite> SELECT strftime(''%s'',''now'');
1393348134
Following command computes the number of days since the signing of the US Declaration of Independence.
sqlite> SELECT julianday(''now'') - julianday(''1776-07-04'');
86798.7094695023
Following command computes the number of seconds since a particular moment in 2004.
sqlite> SELECT strftime(''%s'',''now'') - strftime(''%s'',''2004-01-01 02:34:56'');
295001572
Following command computes the date of the first Tuesday in October for the current year.
sqlite> SELECT date(''now'',''start of year'',''+9 months'',''weekday 2'');
2013-10-01
Following command computes the time since the UNIX epoch in seconds (like strftime(”%s”,”now”) except includes fractional part).
sqlite> SELECT (julianday(''now'') - 2440587.5)*86400.0;
1367926077.12598
To convert between UTC and local time values when formatting a date, use the utc or localtime modifiers as follows −
sqlite> SELECT time(''12:00'', ''localtime'');
05:00:00
sqlite> SELECT time(''12:00'', ''utc'');
19:00:00
”;