Valid Modifiers for SQLite Date/Time Functions

Below is a list of valid modifiers for date and time functions in SQLite.

ModifierExample
NNN daysdate(‘now’, ‘+3 days’)
NNN hoursdatetime(‘now’, ‘-3 hours’)
NNN minutesdatetime(‘now’, ‘+3 minutes’)
NNN.NNNN secondsdatetime(‘now’, ‘-30 seconds’)
NNN monthsdate(‘now’, ‘+3 months’)
NNN yearsdate(‘now’, ‘-3 years’)
start of monthdate(‘now’, ‘start of month’)
start of yeardate(‘now’, ‘start of year’)
start of daydatetime(‘now’, ‘start of day’)
weekday Ndate(‘now’, ‘weekday 6’)
unixepochdatetime(‘1588965525’, ‘unixepoch’)
localtimedatetime(‘now’, ‘localtime’)
utcdatetime(‘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