Sqlite Time Function

SQLite: time Function

This SQLite post explains how to use the SQLite time function with syntax and examples.

Description

The SQLite time function is a very powerful function that can calculate a time and return it in the format 'HH-MM-SS'.

Syntax

The syntax for the time function in SQLite is:

time(timestring, [ modifier1, modifier2, ... modifier_n ] )

Parameters or Arguments

timestring

A time value. It can be one of the following:

timestring Explanation
now now is a literal used to return the current time.
YYYY-MM-DD Date value formatted as 'YYYY-MM-DD'
YYYY-MM-DD HH:MM Date value formatted as 'YYYY-MM-DD HH:MM'
YYYY-MM-DD HH:MM:SS Date value formatted as 'YYYY-MM-DD HH:MM:SS'
YYYY-MM-DD HH:MM:SS.SSS Date value formatted as 'YYYY-MM-DD HH:MM:SS.SSS'
HH:MM Date value formatted as 'HH:MM'
HH:MM:SS Date value formatted as 'HH:MM:SS'
HH:MM:SS.SSS Date value formatted as 'HH:MM:SS.SSS'
YYYY-MM-DDTHH:MM Date value formatted as 'YYYY-MM-DDTHH:MM' where T is a literal character separating the date and time portions
YYYY-MM-DDTHH:MM:SS Date value formatted as 'YYYY-MM-DDTHH:MM:SS' where T is a literal character separating the date and time portions
YYYY-MM-DDTHH:MM:SS.SSS Date value formatted as 'YYYY-MM-DDTHH:MM:SS.SSS' where T is a literal character separating the date and time portions
DDDDDDDDDD Julian date number

modifier1, modifier2, ... modifier_n

Optional. These are modifiers that are applied to the timestring. Each modifier is applied in order and are cumulative. They can be one or more of the following:

modifier Explanation
[+-]NNN years Number of years added/subtracted to the date
[+-]NNN months Number of months added/subtracted to the date
[+-]NNN days Number of days added/subtracted to the date
[+-]NNN hours Number of hours added/subtracted to the date
[+-]NNN minutes Number of minutes added/subtracted to the date
[+-]NNN seconds Number of seconds added/subtracted to the date
[+-]NNN.NNNN seconds Number of seconds (and fractional seconds) added/subtracted to the date
start of year Shifting the date back to the start of the year
start of month Shifting the date back to the start of the month
start of day Shifting the date back to the start of the day
weekday N Moves the date forward to the next date where weekday number is N (0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday)
unixepoch Used with the DDDDDDDDDD timestring to interpret the date as UNIX Time (ie: number of seconds since 1970-01-01)
localtime Adjusts date to localtime, assuming the timestring was expressed in UTC
utc Adjusts date to utc, assuming the timestring was expressed in localtime

Note

  • The time function returns the result displayed as a 'HH-MM-SS' format.

Applies To

The time function can be used in the following versions of SQLite:

  • SQLite 3.8.6, SQLite 3.8.x, SQLite 3.7.x, SQLite 3.6.x

Example

Let's look at some SQLite time function examples and explore how to use the time function in SQLite.

Current Time Example

You could retrieve the current time in SQLite using the "now" timestring with the time function as follows:

sqlite> SELECT time('now');
Output: '17:11:02'           (formatted as HH-MM-SS)

Time Value Example

You could retrieve a time value using the time function as follows:

sqlite> SELECT time('2014-10-16 11:23:02');
Output: '11:23:02'

sqlite> SELECT time('11:23:02.582');
Output: '11:23:02'

sqlite> SELECT time('2014-10-16');
Output: '00:00:00'

In these examples, we've returned the time portion of a date and/or time value. Using the time function, you can pass in a date, time or date/time value. In any case, the time function will strip off the date portion and return the time portion formatted as 'HH-MM-SS'.

Adding/Subtracting Hours Example

You can use the time function to manipulate a time value and add or subtract hours to it. This is done using the 'NNN hours' modifier with the time function as follows:

sqlite> SELECT time('11:23:02','+2 hours');
Output: '13:23:02'

sqlite> SELECT time('11:23:02','-2 hours');
Output: '09:23:02'

sqlite> SELECT time('now','+5 hours');
Output: '15:50:08'           (assuming current time is '10:50:08')

In these examples, we've used the 'NNN hours' modifier to add 2 hours in the first example, subtract 2 hours in the second example, and add 5 hours to the current time in the third example.

Adding/Subtracting Minutes Example

You can use the time function to manipulate a time value and add or subtract minutes to it. This is done using the 'NNN minutes' modifier with the time function as follows:

sqlite> SELECT time('11:15:02','+15 minutes');
Output: '11:30:02'

sqlite> SELECT time('11:15:02','-15 minutes');
Output: '11:00:02'

sqlite> SELECT time('now','+30 minutes');
Output: '11:20:08'           (assuming current time is '10:50:08')

In these examples, we've used the 'NNN minutes' modifier to add 15 minutes in the first example, subtract 15 minutes in the second example, and add 30 minutes to the current time in the third example.