SQLite Date and Time Functions
Description
SQLite supports five date and time functions as follows:
| Name | Syntax |
|---|---|
| date | date(timestring, modifier, modifier, ...) |
| time | time(timestring, modifier, modifier, ...) |
| datetime | datetime(timestring, modifier, modifier, ...) |
| julianday | julianday(timestring, modifier, modifier, ...) |
| strftime | strftime(format, timestring, modifier, modifier, ...) |
The above mentioned date and time functions take a time string as an argument and followed by zero or more modifiers. The strftime() function also takes a format string as its first argument. The date and time functions use a subset date and time formats.
Here is the list of timestring format :
| Name | Description |
|---|---|
| YYYY-MM-DD | Year Month and Date |
| YYYY-MM-DD HH:MM | Year Month Date Hour Minute |
| YYYY-MM-DD HH:MM:SS | Year Month Date Hour Minute Second |
| YYYY-MM-DD HH:MM:SS.SSS | Year Month Date Hour Minute Second miliseconds |
| YYYY-MM-DDTHH:MM | "T" is a literal character separating the date and the time, |
| YYYY-MM-DDTHH:MM:SS | "T" is a literal character separating the date and the time, |
| YYYY-MM-DDTHH:MM:SS.SSS | "T" is a literal character separating the date and the time, |
| HH:MM | specify only a time assume a date of 2000-01-01. |
| HH:MM:SS | specify only a time assume a date of 2000-01-01. |
| HH:MM:SS.SSS | specify only a time assume a date of 2000-01-01. |
| now | current date and time |
| DDDDDDDDDD | A Julian day number expressed as a floating point value. |
Here is the list of modifiers:
| Name | Description |
|---|---|
| NNN days | number of days |
| NNN hours | number of hours |
| NNN minutes | number of minutes |
| NNN.NNNN seconds | numhber of miliseconds |
| NNN months | numher of months |
| NNN years | number of years |
| start of month | The "start of" modifiers shift the date backwards to the beginning of the current month. |
| start of year | The "start of" modifiers shift the date backwards to the beginning of the current year. |
| start of day | The "start of" modifiers shift the date backwards to the beginning of the current day. |
| weekday N | The "weekday" modifier advances the date forward to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth. |
| unixepoch | The "unixepoch" modifier only works if it immediately follows a timestring in the DDDDDDDDDD format. |
| localtime | The "localtime" modifier assumes the time string to its left is in Universal Coordinated Time (UTC) and adjusts the time string so that it displays localtime. |
| utc | The "utc" is the opposite of "localtime". "utc" assumes that the string to its left is in the local timezone and adjusts that string to be in UTC. |
Here is the list of format specifiers:
| Format | Description |
|---|---|
| %d | day of month: 00 |
| %f | fractional seconds: SS.SSS |
| %H | hour: 00-24 |
| %j | day of year: 001-366 |
| %J | Julian day number |
| %m | month: 01-12 |
| %M | minute: 00-59 |
| %s | seconds since 1970-01-01 |
| %S | seconds: 00-59 |
| %w | day of week 0-6 with Sunday==0 |
| %W | week of year: 00-53 |
| %Y | year: 0000-9999 |
| %% | % |