SQLite Time Function
To get a time value, the SQLite time function is used. It returns a time value in ‘HH-MM-SS’ format.
Syntax:
time( time_string, [ modifier1, modifier2, ... modifier_n ] )
Time_string:
| TIMESTRING | USES |
| now | To get the current date. |
| YYYY-MM-DD | To specify the date value formatted as ‘YYYY-MM-DD’. |
| YYYY-MM-DD HH:MM | To specify the date value formatted as ‘YYYY-MM-DD HH:MM’. |
| YYYY-MM-DD HH:MM:SS | To specify the date value formatted as ‘YYYY-MM-DD HH:MM:SS’. |
| YYYY-MM-DD HH:MM:SS.SSS | To specify the date value formatted as ‘YYYY-MM-DD HH:MM:SS.SSS’. |
| HH:MM | To specify the date value formatted as ‘HH:MM’. |
| HH:MM:SS | To specify the date value formatted as ‘HH:MM:SS’. |
| HH:MM:SS.SSS | To specify the date value formatted as ‘HH:MM:SS.SSS’. |
| YYYY-MM-DDTHH:MM | To specify the date value formatted as ‘YYYY-MM-DDTHH:MM’. Here, T is a literal character used to separate the date and time portions. |
| YYYY-MM-DDTHH:MM:SS | To specify the date value formatted as ‘YYYY-MM-DDTHH:MM:SS’. Here, T is a literal character used to separate the date and time portions. |
| YYYY-MM-DDTHH:MM:SS.SSS | To specify the date value formatted as ‘YYYY-MM-DDTHH:MM:SS.SSS’. Here, T is a literal character used to separate the date and time portions. |
| DDDDDDDDDD | To specify the Julian date number. |
Modifier:
Modifiers are used to add or subtract time, date or years.
| MODIFIER | USES |
| [+-]NNN years | Specifies the number of years added/subtracted to the date. |
| [+-]NNN months | Specifies the number of months added/subtracted to the date. |
| [+-]NNN days | Specifies the number of days added/subtracted to the date. |
| [+-]NNN hours | Specifies the number of hours added/subtracted to the date. |
| [+-]NNN minutes | Specifies the number of minutes added/subtracted to the date. |
| [+-]NNN seconds | Specifies the number of seconds added/subtracted to the date. |
| [+-]NNN.NNNN seconds | Specifies the number of seconds (and fractional seconds) added/subtracted to the date. |
| start of year | Shifts the date back to the start of the year. |
| start of month | Shifts the date back to the start of the month. |
| start of day | Shifts the date back to the start of the day. |
| weekday N | 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). |
| unixepoch | Interprets the date as UNIX Time (ie: number of seconds since 1970-01-01). |
| localtime | Adjusts the date to localtime, assuming the time string was expressed in UTC. |
| utc | Adjusts the date to utc, assuming the time string was expressed in localtime. |
Example1:
SELECT time('now'); |
Output:
12:00:04 |
Explanation:
In the above example, we are retrieving the current time.
Example 2:
SELECT time('now','+2 hours’); |
Output:
14:00:04 |
Explanation:
In the above example, we are adding 2 hours to the current time.
Example 3:
SELECT time('12:00:04','+2 hours’); |
Output:
14:00:04 |
Explanation:
In the above example, we are adding 2 hours to the specified time.
Example 4:
SELECT time('now','+2 minutes’); |
Output:
12:02:04 |
Explanation:
In the above example, we are adding 2 minutes to the current time.
Example 5:
SELECT time(‘12:00:04’,'+2 minutes’); |
Output:
12:02:04 |
Explanation:
In the above example, we are adding 2 minutes to the specified time.
Example 6:
SELECT time('now','-2 hours’); |
Output:
10:00:04 |
Explanation:
In the above example, we are subtracting 2 hours from the current time.
Example 7:
SELECT time('12:00:04','-2 hours’); |
Output:
10:00:04 |
Explanation:
In the above example, we are subtracting 2 hours from the specified time.
Example 8:
SELECT time('now','-2 minutes’); |
Output:
11:58:04 |
Explanation:
In the above example, we are subtracting 2 minutes from the current time.
Example 9:
SELECT time(‘12:00:04’,'-2 minutes’); |
Output:
11:58:04 |
Explanation:
In the above example, we are subtracting 2 minutes from the specified time.