SQLite strftime Function
To fetch date and time, the SQLite strftime function is used. It is a very powerful function and is also used to perform date calculations.
Syntax:
strftime (format, timestring [, modifier1, modifier2, ... modifier_n ] )
Format:
| FORMAT | DESCRIPTION | VALUES |
| Year | 0000 to 9999 | |
| Week of year | 00 to 53 | |
| Day of week | 0 to 6, where 0 is Sunday | |
| Month of year | 01 to 12 | |
| Day of month | 00 to 31 | |
| Hour | 00 to 24 | |
| Minute | 00 to 25 | |
| Seconds | 00 to 59 | |
| Seconds | Since 1970-01-01 | |
| Fractional seconds | SS.SSS | |
| Day of year | 001 to 366 | |
| Julian day | Numeric value |
Time_string:
| TIMESTRING | USES |
| now | To get the current date. |
| YYYY-MM-DD | To specify the date value formatted as ‘YYYY-MM-DD’. |
| YYYY-MM-DD HH:MM | To specify the date value formatted as ‘YYYY-MM-DD HH:MM’. |
| YYYY-MM-DD HH:MM:SS | To specify the date value formatted as ‘YYYY-MM-DD HH:MM:SS’. |
| YYYY-MM-DD HH:MM:SS.SSS | To specify the date value formatted as ‘YYYY-MM-DD HH:MM:SS.SSS’. |
| HH:MM | To specify the date value formatted as ‘HH:MM’. |
| HH:MM:SS | To specify the date value formatted as ‘HH:MM:SS’. |
| HH:MM:SS.SSS | To specify the date value formatted as ‘HH:MM:SS.SSS’. |
| YYYY-MM-DDTHH:MM | To specify the date value formatted as ‘YYYY-MM-DDTHH:MM’. Here, T is a literal character used to separate the date and time portions. |
| YYYY-MM-DDTHH:MM:SS | To specify the date value formatted as ‘YYYY-MM-DDTHH:MM:SS’. Here, T is a literal character used to separate the date and time portions. |
| YYYY-MM-DDTHH:MM:SS.SSS | To specify the date value formatted as ‘YYYY-MM-DDTHH:MM:SS.SSS’. Here, T is a literal character used to separate the date and time portions. |
| DDDDDDDDDD | To specify the Julian date number. |
Modifier:
Modifiers are used to add or subtract time, date or years.
| MODIFIER | USES |
| [+-]NNN years | Specifies the number of years added/subtracted to the date. |
| [+-]NNN months | Specifies the number of months added/subtracted to the date. |
| [+-]NNN days | Specifies the number of days added/subtracted to the date. |
| [+-]NNN hours | Specifies the number of hours added/subtracted to the date. |
| [+-]NNN minutes | Specifies the number of minutes added/subtracted to the date. |
| [+-]NNN seconds | Specifies the number of seconds added/subtracted to the date. |
| [+-]NNN.NNNN seconds | Specifies the number of seconds (and fractional seconds) added/subtracted to the date. |
| start of year | Shifts the date back to the start of the year. |
| start of month | Shifts the date back to the start of the month. |
| start of day | Shifts the date back to the start of the day. |
| weekday N | Move the date forward to the next date where weekday number is N (0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday). |
| unixepoch | Interprets the date as UNIX Time (ie: number of seconds since 1970-01-01). |
| localtime | Adjusts the date to localtime, assuming the time string was expressed in UTC. |
| utc | Adjusts the date to utc, assuming the time string was expressed in localtime. |
Example 1:
SELECT strftime('%Y %m %d', 'now'); |
Output:
2019-08-05 |
Explanation: In the above example, we are retrieving the current date.
Example 2:
SELECT strftime('%Y-%m-%d', 'now', 'start of month'); |
Output:
2019-08-01 |
Explanation: In the above example, we are retrieving the first day of the month.
Example 3:
SELECT strftime('%Y-%m-%d', '2019-08-05', 'start of month'); |
Output:
2019-08-01 |
Explanation: In the above example, we are retrieving the first day of the month.
Example 4:
SELECT strftime('%Y-%m-%d', 'now', 'start of month','+1 month', '-1 day'); |
Output:
2019-08-31 |
Explanation: In the above example, we are retrieving the last day of the month.
Example 5:
SELECT strftime('%Y-%m-%d', '2019-08-05', 'start of month','+1 month', '-1 day'); |
Output:
2019-08-31 |
Explanation: In the above example, we are retrieving the last day of the month.
Example 6:
SELECT strftime('%Y-%m-%d', 'now','+2 years'); |
Output:
2021-08-05 |
Explanation: In the above example, we are adding 2 years to the current date.
Example 7:
SELECT strftime('%Y-%m-%d', '2019-08-05','+2 years'); |
Output:
2021-08-05 |
Explanation: In the above example, we are adding 2 years to the specified date.
Example 8:
SELECT strftime('%Y-%m-%d', 'now','+2 days'); |
Output:
2019-08-07 |
Explanation: In the above example, we are adding 2 days to the current date.
Example 9:
SELECT strftime('%Y-%m-%d', '2019-08-05','+2 days'); |
Output:
2019-08-07 |
Explanation: In the above example, we are adding 2 days to the specified date.
Example 10:
SELECT strftime('%Y-%m-%d', '2019-08-05','-2 days'); |
Output:
2019-08-03 |
Explanation: In the above example, we are subtracting 2 days from the specified date.
