SQLite time Function

Summary: in this tutorial, you will learn how to use the SQLite time() function to manipulate time data.

Introduction to SQLite time() function

SQLite time() function accepts a time string and one or more modifiers. It returns a string that represents a specific time in this format: HH:MM:SS.

The following shows the syntax of the time() function:

time(time_string[, modifier, ...])
Code language: SQL (Structured Query Language) (sql)

In this syntax:

  • The time_string can be any valid time string defined by SQLite. For example, the now string returns the current time. The time_string can be specific such as 05:20:30
  • Each modifier transforms the time value on its left. The transformation is from left to right, therefore, the order of modifiers is significant and affect the result of the time() function.

For the information on the time string and modifier, check it out the date() function tutorial.

See the following example:

SELECT  
    time('10:20:30','+2 hours');
Code language: SQL (Structured Query Language) (sql)

The output is:

12:20:30
Code language: SQL (Structured Query Language) (sql)

In this example, the +2 hours modifier adds 2 hours to the time string 10:20:30 that results in 12:20:30.

SQLite time() function examples

Let’s take some examples to get a better understanding of the time() function.

Getting current time example

To get the current time in UTC, you use the following statement:

SELECT 
    time('now');
Code language: SQL (Structured Query Language) (sql)

If you want to get the current local time instead of UTC time, you need to pass localtime modifier to the function:

SELECT 
    time('now','localtime');
Code language: SQL (Structured Query Language) (sql)

Adding time value example

In order to add a time value to a time, you use the +NNN hours, +NNN minutes, +NNN seconds modifiers.

Note that s is optional so you can use +1 hour or +2 hours.

The following example shows how to add 1 hour 20 minutes to a time value:

SELECT
    time('10:20:30','+1 hours','+20 minutes')
Code language: SQL (Structured Query Language) (sql)

The output is:

11:40:30
Code language: SQL (Structured Query Language) (sql)

Subtracting time value example

To subtract a time value from a time, you use the -NNN hours, -NNN minutes, -NNN seconds modifiers.

For example, the following statement illustrates how to subtract 2 hours 15 minutes from a time value:

SELECT
    time('10:20:30','-2 hours','-15 minutes')
Code language: SQL (Structured Query Language) (sql)

Here is the output:

08:05:30
Code language: SQL (Structured Query Language) (sql)

Extracting time data from a datetime value

You can use the time function to extract time data from a datetime value. See the following example:

SELECT 
    time('2018-11-02 15:20:15');
Code language: SQL (Structured Query Language) (sql)

The output is the time part of the date time value:

15:20:15
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQLite time() function to manipulate time data.

Was this tutorial helpful ?