Sqlite Datetime Function
SQLite: datetime Function
This SQLite post explains how to use the SQLite datetime function with syntax and examples.
Description
The SQLite datetime function is a very powerful function that can calculate a date/time value, and return it in the format 'YYYY-MM-DD HH:MM:SS'.
Syntax
The syntax for the datetime function in SQLite is:
datetime(timestring, [ modifier1, modifier2, ... modifier_n ] )Parameters or Arguments
- timestring
- modifier1, modifier2, ... modifier_n
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 |
Note
- The datetime function returns the result displayed as a 'YYYY-MM-DD HH:MM:SS' format.
Applies To
The datetime 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 datetime function examples and explore how to use the datetime function in SQLite.
Current Date and Time Example
You could retrieve the current date and time in SQLite using the "now" timestring with the datetime function as follows:
sqlite> SELECT datetime('now');
Output: '2014-10-23 15:21:07' (formatted as YYYY-MM-DD HH:MM:SS)Adding/Subtracting Years Example
You can use the datetime function to manipulate a date/time value and add or subtract years to it. This is done using the 'NNN years' modifier with the datetime function as follows:
sqlite> SELECT datetime('2014-10-23','+2 years');
Output: '2016-10-23 00:00:00'
sqlite> SELECT datetime('2012-10-23 09:23:10','-2 years');
Output: '2010-10-23 09:23:10'
sqlite> SELECT datetime('now','+5 years');
Output: '2019-10-23 09:23:10' (assuming current date is 2014-10-23 09:23:10)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/time in the third example.
Adding/Subtracting Days Example
You can use the datetime function to manipulate a date/time value and add or subtract days to it. This is done using the 'NNN days' modifier with the datetime function as follows:
sqlite> SELECT datetime('2014-10-23','+7 days');
Output: '2014-10-30 00:00:00'
sqlite> SELECT datetime('2014-10-23 09:23:10','-7 days');
Output: '2014-10-16 09:23:10'
sqlite> SELECT datetime('now','+10 days');
Output: '2014-11-02 09:23:10' (assuming current date is 2014-10-23 09:23:10)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/time in the third example.
Adding/Subtracting Hours Example
You can use the datetime function to manipulate a date/time value and add or subtract hours to it. This is done using the 'NNN hours' modifier with the datetime function as follows:
sqlite> SELECT datetime('2014-10-23 11:23:02','+2 hours');
Output: '2014-10-23 13:23:02'
sqlite> SELECT datetime('2014-10-23 11:23:02','-2 hours');
Output: '2014-10-23 09:23:02'
sqlite> SELECT datetime('now','+5 hours');
Output: '2014-10-23 14:23:10' (assuming current date is 2014-10-23 09:23:10)In these examples, we've used the 'NNN hours' modifier to add 2 hours in the first example, subtract 2 hours in the second example, and add 5 hours to the current date/time in the third example.
Adding/Subtracting Minutes Example
You can use the datetime function to manipulate a date/time value and add or subtract minutes to it. This is done using the 'NNN minutes' modifier with the datetime function as follows:
sqlite> SELECT datetime('2014-10-23 11:15:02','+15 minutes');
Output: '2014-10-23 11:30:02'
sqlite> SELECT datetime('2014-10-23 11:15:02','-15 minutes');
Output: '2014-10-23 11:00:02'
sqlite> SELECT datetime('now','+30 minutes');
Output: '2014-10-23 09:53:10' (assuming current date is 2014-10-23 09:23:10)In these examples, we've used the 'NNN minutes' modifier to add 15 minutes in the first example, subtract 15 minutes in the second example, and add 30 minutes to the current date/time in the third example.
