SQLite has a time() function that enables you to return a time value based on a time string and any modifiers.

It returns the time in this format: HH:MM:SS

To use this function, you need to provide a time string, plus any (optional) modifiers. A modifier allows you to change the time, such as add a number of hours, set it to local time, etc

Syntax

The syntax of the time() function goes like this:

time(timestring, modifier, modifier, ...)

The timestring argument must be a valid time string.

The modifier arguments are optional. You can provide one or more modifiers. If you provide a modifier, it must be a valid modifier.

Example

Here’s a basic example to demonstrate the time() function being used with one argument.

SELECT time('now');

Result:

03:47:36

The now time string is converted into the current date and time. Given I’m using the time() function, only the time part is returned.

Add a Modifier

We can modify the previous result by using a modifier. Here’s an example.

SELECT time('now', '+5 hours');

Result:

08:48:24

In this case, I added five hours to the time.

Multiple Modifiers

As mentioned, you can add one or more modifiers. Here’s an example of adding another modifier to the previous example.

SELECT time('now', '+5 hours', 'localtime');

Result:

18:50:08

The localtime modifier assumes the time string provided is in Universal Coordinated Time (UTC). It then adjusts the time string so that it displays local time.

Therefore, you may get a different result, depending on your local time.

String Literal as the Time String

The above examples use now as the time string, but you can provide any valid time string.

Here’s a few other examples.

SELECT time('1999-12-18 17:10:23');

Result:

17:10:23 

In this case it simply removes the date portion from the date.

time() vs strftime()

The time() function returns exactly the same result that strftime('%H:%M:%S', ...) returns. The time() function is just a more convenient way to do it.

SELECT 
  time('now'),
  strftime('%H:%M:%S', 'now');

Result:

time('now')  strftime('%H:%M:%S', 'now')
-----------  ---------------------------
03:56:09     03:56:09                   

Date Range

As with all SQLite date and time functions, time() only works for dates between 0000-01-01 00:00:00 and 9999-12-31 23:59:59 (julian day numbers 1721059.5 through 5373484.5).

For dates outside that range, the results are undefined.