SQLite time function is used to calculate time and return time in ‘HH-MM-SS’ format.
Syntax:
time(timestring, [ modifier1, modifier2, ... modifier_n ] ) Here, timestring is a date value which can be anyone of the following:
| Index | timestring | Description |
|---|---|---|
| 1) | now | It is a literal used to return the current date. |
| 2) | YYYY-MM-DD | It specifies the date value formatted as ‘YYYY-MM-DD’ |
| 3) | YYYY-MM-DD HH:MM | It specifies the date value formatted as ‘YYYY-MM-DD HH:MM’ |
| 4) | YYYY-MM-DD HH:MM:SS | It specifies the date value formatted as ‘YYYY-MM-DD HH:MM:SS’ |
| 5) | YYYY-MM-DD HH:MM:SS.SSS | It specifies the date value formatted as ‘YYYY-MM-DD HH:MM:SS.SSS’ |
| 6) | HH:MM | It specifies the date value formatted as ‘HH:MM’ |
| 7) | HH:MM:SS | It specifies the date value formatted as ‘HH:MM:SS’ |
| 8) | HH:MM:SS.SSS | It specifies the date value formatted as ‘HH:MM:SS.SSS’ |
| 9) | YYYY-MM-DDTHH:MM | It specifies the date value formatted as ‘YYYY-MM-DDTHH:MM’ where t is a literal character separating the date and time portions. |
| 10) | YYYY-MM-DDTHH:MM:SS | It specifies the date value formatted as ‘YYYY-MM-DDTHH:MM:SS’ where t is a literal character separating the date and time portions |
| 11) | YYYY-MM-DDTHH:MM:SS.SSS | It specifies the date value formatted as ‘YYYY-MM-DDTHH:MM:SS.SSS’ where t is a literal character separating the date and time portions |
| 12) | DDDDDDDDDD | It specifies the Julian date number |
modifier1, modifier2, … modifier_n: modifiers are optional. These are used with timestring to add or subtract time, date or years.
| Index | Modifier | Description |
|---|---|---|
| 1) | [+-]NNN years | It is used to specify number of years added/subtracted to the date |
| 2) | [+-]NNN months | It is used to specify number of months added/subtracted to the date |
| 3) | [+-]NNN days | It is used to specify number of days added/subtracted to the date |
| 4) | [+-]NNN hours | It is used to specify number of hours added/subtracted to the date |
| 5) | [+-]NNN minutes | It is used to specify number of minutes added/subtracted to the date |
| 6) | [+-]NNN seconds | It is used to specify number of seconds added/subtracted to the date |
| 7) | [+-]NNN.NNNN seconds | It is used to specify number of seconds (and fractional seconds) added/subtracted to the date |
| 8) | start of year | It is used to shift the date back to the start of the year |
| 9) | start of month | It is used to shift the date back to the start of the month |
| 10) | start of day | It is used to shift the date back to the start of the day |
| 11) | weekday N | It is used to move 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) |
| 12) | unixepoch | It is used with the DDDDDDDDDD timestring to interpret the date as UNIX Time (ie: number of seconds since 1970-01-01) |
| 13) | localtime | It is used to adjust date to localtime, assuming the timestring was expressed in UTC |
| 14) | utc | It is used to adjust date to utc, assuming the timestring was expressed in localtime |
Example1: Retrieve current time:
SELECT time('now'); Output:
Example2: Add/ subtract hours and minutes to current time:
SELECT time('11:23:02','-2 hours');
SELECT time('now','+5 hours');
SELECT time('11:15:02','+15 minutes');
SELECT time('now','-30 minutes'); Output:
Leave a Reply