Sqlite Strftime Function
SQLite: strftime Function
This SQLite post explains how to use the SQLite strftime function with syntax and examples.
Description
The SQLite strftime function is a very powerful function that allows you to return a formatted date as well as perform date calculations on that date. This function returns the date as a text representation. It is modeled after the strftime C function but with some differences.
Syntax
The syntax for the strftime function in SQLite is:
strftime(format, timestring [, modifier1, modifier2, ... modifier_n ] )Parameters or Arguments
- format
- timestring
- modifier1, modifier2, ... modifier_n
The format string to apply which can be:
| Format | Explanation |
|---|---|
| %d | Day of the month (1-31) |
| %f | Seconds with fractional seconds (SS.sss) |
| %H | Hour on 24-hour clock (00-23) |
| %j | Day of the year (001-366) |
| %J | Julian day number (DDDDDDD.ddddddd) |
| %m | Month (01-12) |
| %M | Minute (00-59) |
| %s | Seconds since 1970-01-01 |
| %S | Seconds (00-59) |
| %w | Weekday (0-6) (0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday) |
| %W | Week number in the year (00-53) The first Monday is the beginning of week 1. |
| %Y | Year with century (yyyy) |
| %% | % as a literal |
A date value. It can be one of the following:
| timestring | Explanation |
|---|---|
| now | now is a literal used to return the current date |
| YYYY-MM-DD | Date value formatted as 'YYYY-MM-DD' |
| YYYY-MM-DD HH:MM | Date value formatted as 'YYYY-MM-DD HH:MM' |
| YYYY-MM-DD HH:MM:SS | Date value formatted as 'YYYY-MM-DD HH:MM:SS' |
| YYYY-MM-DD HH:MM:SS.SSS | Date value formatted as 'YYYY-MM-DD HH:MM:SS.SSS' |
| HH:MM | Date value formatted as 'HH:MM' |
| HH:MM:SS | Date value formatted as 'HH:MM:SS' |
| HH:MM:SS.SSS | Date value formatted as 'HH:MM:SS.SSS' |
| YYYY-MM-DDTHH:MM | Date value formatted as 'YYYY-MM-DDTHH:MM' where T is a literal character separating the date and time portions |
| YYYY-MM-DDTHH:MM:SS | Date value formatted as 'YYYY-MM-DDTHH:MM:SS' where T is a literal character separating the date and time portions |
| YYYY-MM-DDTHH:MM:SS.SSS | Date value formatted as 'YYYY-MM-DDTHH:MM:SS.SSS' where T is a literal character separating the date and time portions |
| DDDDDDDDDD | Julian date number |
Optional. These are modifiers that are applied to the timestring. Each modifier is applied in order and are cumulative. They can be one or more of the following:
| modifier | Explanation |
|---|---|
| [+-]NNN years | Number of years added/subtracted to the date |
| [+-]NNN months | Number of months added/subtracted to the date |
| [+-]NNN days | Number of days added/subtracted to the date |
| [+-]NNN hours | Number of hours added/subtracted to the date |
| [+-]NNN minutes | Number of minutes added/subtracted to the date |
| [+-]NNN seconds | Number of seconds added/subtracted to the date |
| [+-]NNN.NNNN seconds | Number of seconds (and fractional seconds) added/subtracted to the date |
| start of year | Shifting the date back to the start of the year |
| start of month | Shifting the date back to the start of the month |
| start of day | Shifting the date back to the start of the day |
| weekday N | Moves 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 | Used with the DDDDDDDDDD timestring to interpret the date as UNIX Time (ie: number of seconds since 1970-01-01) |
| localtime | Adjusts date to localtime, assuming the timestring was expressed in UTC |
| utc | Adjusts date to utc, assuming the timestring was expressed in localtime |
Applies To
The strftime function can be used in the following versions of SQLite:
- SQLite 3.8.6, SQLite 3.8.x, SQLite 3.7.x, SQLite 3.6.x
Example
Let's look at some SQLite strftime function examples and explore how to use the strftime function in SQLite.
Current Date Example
You could retrieve the current date in SQLite using the "now" timestring with the strftime function as follows:
sqlite> SELECT strftime('%Y %m %d', 'now');
Output: '2017 03 07' (formatted as YYYY MM DD)
sqlite> SELECT strftime('%Y-%m-%d %H:%M', 'now');
Output: '2017-03-07 20:14' (formatted as YYYY-MM-DD HH:MM)First Day of the Month Example
You could retrieve the first day of the month using the strftime function as follows:
sqlite> SELECT strftime('%Y-%m-%d', '2016-10-16', 'start of month');
Output: '2016-10-01'
sqlite> SELECT strftime('%Y-%m-%d', 'now', 'start of month');
Output: '2017-03-01' (assuming current date is 2017-03-07)
sqlite> SELECT strftime('%Y-%m-%d', '2017-03-07', '-6 days');
Output: '2017-03-01'
sqlite> SELECT strftime('%Y-%m-%d', 'now', '-6 days');
Output: '2017-03-01' (assuming current date is 2017-03-07)In these examples, we've calculated the first day of the month in a few different ways. The 'start of month' modifier lets us take any date value and calculate the first day of the month for that date. This includes using the 'now' timestring.
We can also add or subtract days to a date value using the 'NNN days' modifier. In our examples, we subtracted 6 days from our date values. Since we had a date value of the 2017-03-07, this returns the first day of the month (ie: 2017-03-01).
Last Day of the Month Example
You could retrieve the last day of the month using the strftime function as follows:
sqlite> SELECT strftime('%Y-%m-%d', '2017-03-07', 'start of month', '+1 month', '-1 day');
Output: '2017-03-31'
sqlite> SELECT strftime('%Y-%m-%d', 'now', 'start of month', '+1 month', '-1 day');
Output: '2017-03-31' (assuming current date is 2017-03-07)
sqlite> SELECT strftime('%Y-%m-%d', '2017-03-07', '+24 days');
Output: '2017-03-31'
sqlite> SELECT strftime('%Y-%m-%d', 'now', '+24 days');
Output: '2017-03-31' (assuming current date is 2017-03-07)In these examples, we've calculated the last day of the month in a few different ways. First, we use the 'start of month' modifier to calculate the first day of the month and then add 1 month and then subtract 1 day.
We can also add or subtract days to a date value using the 'NNN days' modifier. In our examples, we added 24 days to our date values. Since we had a date value of the 2017-03-07, this returns the last day of the month (ie: 2017-03-31).
Adding/Subtracting Years Example
You can use the strftime function to manipulate a date value and add or subtract years to it. This is done using the 'NNN years' modifier with the strftime function as follows:
sqlite> SELECT strftime('%Y-%m-%d', '2016-10-16', '+2 years');
Output: '2018-10-16'
sqlite> SELECT strftime('%Y-%m-%d', '2012-10-16', '-2 years');
Output: '2010-10-16'
sqlite> SELECT strftime('%Y-%m-%d', 'now', '+5 years');
Output: '2022-03-07' (assuming current date is 2017-03-07)In these examples, we've used the 'NNN years' modifier to add 2 years in the first example, subtract 2 years in the second example, and add 5 years to the current date in the third example.
Adding/Subtracting Days Example
You can use the strftime function to manipulate a date value and add or subtract days to it. This is done using the 'NNN days' modifier with the strftime function as follows:
sqlite> SELECT strftime('%Y-%m-%d', '2017-03-07', '+7 days');
Output: '2017-03-14'
sqlite> SELECT strftime('%Y-%m-%d', '2017-03-07', '-7 days');
Output: '2017-02-28'
sqlite> SELECT strftime('%Y-%m-%d', 'now', '+10 days');
Output: '2017-03-17' (assuming current date is 2017-03-07)In these examples, we've used the 'NNN days' modifier to add 7 days in the first example, subtract 7 days in the second example, and add 10 days to the current date in the third example.
