Here we will learn sqlite date and time functions with examples and different type of date & time functions (date, time, datetime, julianday, strftime) and change the format of date & times (dd/mm/yyyy, YYYY-MM-DD, etc) with examples.
In SQLite, we don't have any storage class to store Date and time values because of SQLite offers some date functions and time literals that can store date & time as TEXT, REAL, or INTEGER.
Following are the different types of Date and Time functions available in SQLite.
Generally, the SQLite Date & Time functions syntaxes will be like as shown following to get only date or time or datetime, etc. based on our requirements.
date(datetimestring, [modifier1, modifier2…, modifierN])
or
time(datetimestring, [modifier1, modifier2…, modifierN])
or
datetime(datetimestring, [modifier1, modifier2…, modifierN])
Here the 1st argument datetimestring will accept the values in the following formats.
| Format | Example |
|---|---|
| now | 2016-08-30 |
| YYYY-MM-DD | 2016-08-30 |
| YYYY-MM-DD HH:MM | 2016-08-30 18:47 |
| YYYY-MM-DD HH:MM:SS | 2016-08-30 18:47:56 |
| YYYY-MM-DD HH:MM:SS.sss | 2016-08-30 18:47:56.235 |
| YYYY-MM-DDTHH:MM | T literal is used to separate date & time |
| YYYY-MM-DDTHH:MM:SS | T literal is used to separate date & time |
| YYYY-MM-DDTHH:MM:SS.sss | T literal is used to separate date & time |
| HH:MM | 18:50 |
| HH:MM:SS | 06:52:25 |
| HH:MM:SS.sss | 06:26:56.123 |
| DDDDDDD | Julian date number |
| DDDDDDD.ddddddd | Julian date number with a fractional part |
The 2nd parameter modifiers (modifier1, modifier2, … etc.) will accept the values in the following format
| Format | Example |
|---|---|
| XX days | 02 days |
| XX hours | 10 hours |
| XX minutes | 15 minutes |
| XX.XXXX seconds | 10.15 seconds |
| XX months | 04 months |
| XX years | 02 years |
| start of month | start of month |
| start of year | start of year |
| start of day | start of day |
| weekday X | weekday 2 |
| unixepoch | unixepoch |
| localtime | localtime |
| utc | utc |
We will learn all these date and time functions in detailed manner with examples in next chapters.