Time() Function in SQLite

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.