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