SQLite time() Function

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

Syntax

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

time(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 time() function returns a time string in HH:MM:SS format. If no arguments are provided, the time() function returns the current time.

Examples

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

• Get the current time using the SQLite time() function:

  SELECT time();
  

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

  SELECT time('now');
  

• Use the SQLite time() function to get the time part of a datetime value:

  SELECT time('2022-07-26 12:01:01');
  

  time('2022-07-26 12:01:01')
  ---------------------------
  12:01:01

• Add 10 minutes to a fixed time value:

  SELECT time('2022-07-26 08:03:02') AS original,
         time('2022-07-26 08:03:02', '10 minutes') AS ten_minutes_later;
  

  original  ten_minutes_later
  --------  -----------------
  08:03:02  08:13:02