CodesJava

Easy learning with example program codes

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:

TIMESTRINGUSES
nowTo get the current date.
YYYY-MM-DDTo specify the date value formatted as ‘YYYY-MM-DD’.
YYYY-MM-DD HH:MMTo specify the date value formatted as ‘YYYY-MM-DD HH:MM’.
YYYY-MM-DD HH:MM:SSTo specify the date value formatted as ‘YYYY-MM-DD HH:MM:SS’.
YYYY-MM-DD HH:MM:SS.SSSTo specify the date value formatted as ‘YYYY-MM-DD HH:MM:SS.SSS’.
HH:MMTo specify the date value formatted as ‘HH:MM’.
HH:MM:SSTo specify the date value formatted as ‘HH:MM:SS’.
HH:MM:SS.SSSTo specify the date value formatted as ‘HH:MM:SS.SSS’.
YYYY-MM-DDTHH:MMTo 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:SSTo 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.SSSTo 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.
DDDDDDDDDDTo specify the Julian date number.

 

Modifier:

Modifiers are used to add or subtract time, date or years.

MODIFIERUSES
[+-]NNN yearsSpecifies the number of years added/subtracted to the date.
[+-]NNN monthsSpecifies the number of months added/subtracted to the date.
[+-]NNN daysSpecifies the number of days added/subtracted to the date.
[+-]NNN hoursSpecifies the number of hours added/subtracted to the date.
[+-]NNN minutesSpecifies the number of minutes added/subtracted to the date.
[+-]NNN secondsSpecifies the number of seconds added/subtracted to the date.
[+-]NNN.NNNN secondsSpecifies the number of seconds (and fractional seconds) added/subtracted to the date.
start of yearShifts the date back to the start of the year.
start of monthShifts the date back to the start of the month.
start of dayShifts the date back to the start of the day.
weekday NMove 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).
unixepochInterprets the date as UNIX Time (ie: number of seconds since 1970-01-01).
localtimeAdjusts the date to localtime, assuming the time string was expressed in UTC.
utcAdjusts 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.

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status