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

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

modifier1, modifier2, ... modifier_n

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.