Below is a list of valid modifiers for date and time functions in SQLite.
Modifier | Example |
---|---|
NNN days | date(‘now’, ‘+3 days’) |
NNN hours | datetime(‘now’, ‘-3 hours’) |
NNN minutes | datetime(‘now’, ‘+3 minutes’) |
NNN.NNNN seconds | datetime(‘now’, ‘-30 seconds’) |
NNN months | date(‘now’, ‘+3 months’) |
NNN years | date(‘now’, ‘-3 years’) |
start of month | date(‘now’, ‘start of month’) |
start of year | date(‘now’, ‘start of year’) |
start of day | datetime(‘now’, ‘start of day’) |
weekday N | date(‘now’, ‘weekday 6’) |
unixepoch | datetime(‘1588965525’, ‘unixepoch’) |
localtime | datetime(‘now’, ‘localtime’) |
utc | datetime(‘now’, ‘utc’) |
How do these Modifiers Work?
When using one of the date/time functions in SQLite, you have the option of using modifiers to transform the date provided to the function.
For example, you can use a modifier to add a number of days, hours, or even minutes to a given date. Or you could use a modifier to specify UTC, or local time, etc.
Example
Here’s a quick example to show the output from using the above modifiers.
.mode line
SELECT
datetime('now'),
date('now', '+3 days'),
datetime('now', '+3 hours'),
datetime('now', '+3 minutes'),
datetime('now', '+30 seconds'),
date('now', '+3 months'),
date('now', '-3 years'),
date('now', 'start of month'),
date('now', 'start of year'),
datetime('now', 'start of day'),
date('now', 'weekday 6'),
datetime('1588965525', 'unixepoch'),
datetime('now', 'localtime'),
datetime('now', 'utc');
Result:
datetime('now') = 2020-04-26 00:53:53 date('now', '+3 days') = 2020-04-29 datetime('now', '+3 hours') = 2020-04-26 03:53:53 datetime('now', '+3 minutes') = 2020-04-26 00:56:53 datetime('now', '+30 seconds') = 2020-04-26 00:54:23 date('now', '+3 months') = 2020-07-26 date('now', '-3 years') = 2017-04-26 date('now', 'start of month') = 2020-04-01 date('now', 'start of year') = 2020-01-01 datetime('now', 'start of day') = 2020-04-26 00:00:00 date('now', 'weekday 6') = 2020-05-02 datetime('1588965525', 'unixepoch') = 2020-05-08 19:18:45 datetime('now', 'localtime') = 2020-04-26 10:53:53 datetime('now', 'utc') = 2020-04-25 14:53:53