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, thenow
string returns the current time. Thetime_string
can be specific such as05: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 thetime()
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.