SQLite datetime() Function

The SQLite datetime() function converts a time value specified by a time value and modifiers to a datetime string in YYYY-MM-DD HH:MM:SS format.

Syntax

Here is the syntax of the SQLite datetime() function:

datetime(time_value [, modifier, modifier, ...])

Parameters

time_value

Optional. A time value. The time value can be in any of the following formats, as shown below. The value is usually a string, but in the case of format 12 it can be an integer or a floating point number.

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now - the current date and time
12. DDDDDDDDDD.dddddd - Julian days number with fractional part

modifier

Optional. You can use zero or more modifiers to change the time value time_value. Multiple modifiers are applied sequentially from left to right. You can use modifiers like this:

1. NNN days- Add NNN days to the time value
2. NNN hours- Add NNN hours to the time value
3. NNN minutes- Add NNN minutes to the time value
4. NNN.NNNN seconds- Add NNN.NNNN seconds to the time value
5. NNN months- Add NNN months to the time value
6. NNN years- Add NNN years to the time value
7. start of month - Move the time value to the start of its month.
8. start of year - Move the time value to the start of its year.
9. start of day - Move the time value to the start of its day.
10. weekday N - Advance to weekday N (Sunday is 0); if already on that day, leave the date unchanged.
11. unixepoch - Interpret the immediately preceding numeric time value as Unix seconds.
12. julianday - Interpret the immediately preceding numeric time value as a Julian day number.
13. auto - Interpret a numeric time value as a Julian day number or Unix timestamp based on its magnitude; Unix timestamps in the first 63 days of 1970 are ambiguous.
14. localtime - Assume the preceding time value is UTC and convert it to local time.
15. utc - Assume the preceding time value is local time and convert it to UTC.
16. subsec or subsecond (SQLite 3.42.0+) - Return fractional seconds from time(), datetime(), unixepoch(), and strftime('%s'); no effect on date() or julianday().
17. ceiling and floor (SQLite 3.46.0+) - Resolve ambiguous month/year shifts; ceiling is the default, while floor chooses the last day of the previous month.

The NNN represents a number. Can be a positive or negative number. If NNN is negative, it means subtraction.

The unixepoch, julianday, and auto modifiers must immediately follow the initial numeric time value. See the SQLite date and time function documentation for ordering rules and edge cases.

Return value

The SQLite datetime() function returns a datetime string in YYYY-MM-DD HH:MM:SS format. If no arguments are provided, the datetime() function returns the current date and time.

Examples

Here are examples that show how to use the SQLite datetime() function. When the time-value is omitted or set to 'now', SQLite uses the current UTC time, so the exact result changes over time.

• Use SQLite datetime() function to get current datetime:

  SELECT datetime();
  

  Alternatively, you can use the SQLite datetime() function with a time value 'now' to get the current datetime:

  SELECT datetime('now');
  

• Add two hours to a fixed datetime value:

  SELECT datetime('2022-07-26 08:10:05') AS original,
         datetime('2022-07-26 08:10:05', '2 hours') AS two_hours_later;
  

  original             two_hours_later
  -------------------  -------------------
  2022-07-26 08:10:05  2022-07-26 10:10:05