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- Fall back to the beginning of the month in time_value
  8. start of year- Fall back to the beginning of the year in time_value
  9. start of day- Fall back to the beginning of the day in time_value
  10. weekday N- Advance time_value ​​to weekday N
  11. unixepoch- Unix timestamp for now
  12. julianday- The Julian calendar days for now
  13. auto
  14. localtime- The current time
  15. utc- The utc time

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

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 some examples to show usages of the SQLite datetime() function.

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

    SELECT datetime();

    datetime()
-------------------
2022-07-26 08:05:30

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

    SELECT datetime('now');

    datetime('now')
-------------------
2022-07-26 08:05:48

  • Use the SQLite datetime() function to get the datetime after 2 hours:

    SELECT datetime(), datetime('now', '2 hours');

    datetime()           datetime('now', '2 hours')
-------------------  --------------------------
2022-07-26 08:10:05  2022-07-26 10:10:05