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
- modifier1, modifier2, ... modifier_n
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 |
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.
