The SQLite TIME() function returns the time as text in 'HH:MM:SS' format.
Syntax
TIME(time-value, modifier, modifier, ...)
Parameters
time-value |
Required. Specify a time-value. It can be one of the following:
| time-value | Description |
|---|
| 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' |
| YYYY-MM-DDTHH:MM | Date value formatted as 'YYYY-MM-DDTHH:MM' where T is a literal character separating the date and the time |
| YYYY-MM-DDTHH:MM:SS | Date value formatted as 'YYYY-MM-DDTHH:MM:SS' where T is a literal character separating the date and the time |
| 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 the time |
| 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' |
| now | now is a literal used to return the current date. |
| DDDDDDDDDD | Julian date number |
|
modifier, modifier, ... |
Optional. Specify modifiers. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right and are cumulative. The available modifiers are as follows:
| modifier | Description |
|---|
| [+-]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.NNNN seconds | Number of seconds (and fractional seconds) added/subtracted to the date |
| [+-]NNN months | Number of months added/subtracted to the date |
| [+-]NNN years | Number of years added/subtracted to the date |
| start of month | Shifting the date back to the start of the month |
| start of year | Shifting the date back to the start of the year |
| 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 format to interpret the date as UNIX Time (ie: number of seconds since 1970-01-01) |
| julianday | Used with the DDDDDDDDDD format to force the time-value number to be interpreted as a julian-day number |
| auto | Used with the DDDDDDDDDD format to interpret the time-value as either a julian day number or a unix timestamp, depending on its magnitude. |
| localtime | Adjusts date to localtime, assuming the time-value was expressed in UTC |
| utc | Adjusts date to utc, assuming the time-value was expressed in localtime |
|
Return Value
Returns the time value as text in 'HH:MM:SS' format.
Example: Current time
The SQLite 'now' time-value can be used to get the current time.
SELECT TIME('now');
Result: '17:04:32'
Example: Adding/Subtracting hours
To add/subtract hours from a time-value, [+-]NNN hours modifier can be used. In the example below 5 hours is added and subtracted from a given time-value.
SELECT TIME('08:23:19', '+5 hours');
Result: '13:23:19'
SELECT TIME('08:23:19', '-5 hours');
Result: '03:23:19'
Example: Adding/Subtracting minutes
To add/subtract minutes from a time-value, [+-]NNN minutes modifier can be used. In the example below 30 minutes is added and subtracted from a given time-value.
SELECT TIME('08:23:19', '+30 minutes');
Result: '08:53:19'
SELECT TIME('08:23:19', '-30 minutes');
Result: '07:53:19'
Example: Adding/Subtracting seconds
To add/subtract seconds from a time-value, [+-]NNN seconds modifier can be used. In the example below 30 seconds is added and subtracted from a given time-value.
SELECT TIME('08:23:19', '+30 seconds');
Result: '08:23:49'
SELECT TIME('08:23:19', '-30 seconds');
Result: '08:22:49'
❮ SQLite Functions
