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.
The format string to apply which can be:
Format
Explanation
Weekday (0-6)
(0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday)
Week number in the year (00-53)
The first Monday is the beginning of week 1.
Year with century (
yyyy
)
% as a literal
YYYY-MM-DD
T
HH:MM
Date value formatted as 'YYYY-MM-DD
T
HH:MM' where
T
is a literal character separating the date and time portions
YYYY-MM-DD
T
HH:MM:SS
Date value formatted as 'YYYY-MM-DD
T
HH:MM:SS' where
T
is a literal character separating the date and time portions
YYYY-MM-DD
T
HH:MM:SS.SSS
Date value formatted as 'YYYY-MM-DD
T
HH:MM:SS.SSS' where
T
is a literal character separating the date and time portions
DDDDDDDDDD
Julian date number
[+-]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
Adjusts date to utc, assuming the timestring was expressed in localtime
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');
Result:
'2017 03 07'
(formatted as YYYY MM DD)
sqlite> SELECT strftime('%Y-%m-%d %H:%M', 'now');
Result:
'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');
Result: '2016-10-01'
sqlite> SELECT strftime('%Y-%m-%d', 'now', 'start of month');
Result: '2017-03-01' (assuming current date is 2017-03-07)
sqlite> SELECT strftime('%Y-%m-%d', '2017-03-07', '-6 days');
Result: '2017-03-01'
sqlite> SELECT strftime('%Y-%m-%d', 'now', '-6 days');
Result: '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');
Result: '2017-03-31'
sqlite> SELECT strftime('%Y-%m-%d', 'now', 'start of month', '+1 month', '-1 day');
Result: '2017-03-31' (assuming current date is 2017-03-07)
sqlite> SELECT strftime('%Y-%m-%d', '2017-03-07', '+24 days');
Result: '2017-03-31'
sqlite> SELECT strftime('%Y-%m-%d', 'now', '+24 days');
Result: '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');
Result: '2018-10-16'
sqlite> SELECT strftime('%Y-%m-%d', '2012-10-16', '-2 years');
Result: '2010-10-16'
sqlite> SELECT strftime('%Y-%m-%d', 'now', '+5 years');
Result: '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');
Result: '2017-03-14'
sqlite> SELECT strftime('%Y-%m-%d', '2017-03-07', '-7 days');
Result: '2017-02-28'
sqlite> SELECT strftime('%Y-%m-%d', 'now', '+10 days');
Result: '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.
