SQLite Time() Function

  Total Views : 13
  Comments : 0
 
  Keywords : SQLite time() function query with example, Use sqlite time() function to get only time from datetime string with example, SQLite time() function to get time hh:mm:ss format with example

Here we will learn SQLite time() function with examples and how to use SQLite time() function to get only time from given datetime string, get time after 45 minutes or get time before 30 seconds with examples.

SQLite time() Function

In SQLite time() function is used to get only time from the given datetime string.

Syntax of SQLite time() Function

Following is the syntax of SQLite time() function to get only time from given datetime string.

 

time(datetimestring, [modifier1, modifier2…, modifierN])

The SQLite time() function will take datetimestring and one or more modifier values and returns time in a HH:MM:SS format. It is important to note that this function will return TEXT as an output.

 

In above SQLite date() syntax the 1st argument datetimestring will accept the values in following formats.

 

FormatExample
now 2016-08-30
YYYY-MM-DD 2016-08-30
YYYY-MM-DD HH:MM 2016-08-30 18:47
YYYY-MM-DD HH:MM:SS 2016-08-30 18:47:56
YYYY-MM-DD HH:MM:SS.sss 2016-08-30 18:47:56.235
YYYY-MM-DDTHH:MM T literal is used to separate date & time
YYYY-MM-DDTHH:MM:SS T literal is used to separate date & time
YYYY-MM-DDTHH:MM:SS.sss T literal is used to separate date & time
HH:MM 18:50
HH:MM:SS 06:52:25
HH:MM:SS.sss 06:26:56.123
DDDDDDD Julian date number
DDDDDDD.ddddddd Julian date number with fractional part

The 2nd parameter modifiers (modifier1, modifier2, … etc.) will accept the values in following format

 

FormatExample
XX days 02 days
XX hours 10 hours
XX minutes 15 minutes
XX.XXXX seconds 10.15 seconds
XX months 04 months
XX years 02 years
start of month start of month
start of year start of year
start of day start of day
weekday X weekday 2
unixepoch unixepoch
localtime localtime
utc utc

Examples of SQLite time() Function

We will see how to use SQLite time() function to get only time from given datetime string with different examples.

 

Following is the example of getting only current time by using SQLite time() function. We can get current time in 2 ways. By using only time() function without argument or we can also use modifier “now” as shown following.

 

sqlite> SELECT time() as 'Current Time';

 

Current Time

------------

05:31:46

 

Or

 

sqlite> SELECT time('now') as 'Current Time';

 

Current Time

------------

05:31:46

The following SQLite statement will return only time portion from the given date-time value “2016-09-02 12:56:07.01256”.

 

sqlite> SELECT time('2016-09-02 12:56:07.01256') as Time;

 

Time

----------

12:56:07

If you want to get time after 45 minutes from the current time, then we need to write the SQLite query like as shown following.

 

sqlite> SELECT time(),time('now','+45 minutes');

 

time()      time('now','+45 minutes')

----------  -------------------------

05:36:58    06:21:58

If you want to get the time after 30 seconds from the current time the following sqlite query can be used.

 

sqlite> SELECT time(), time('now','+30 seconds');

 

time()      time('now','+30 seconds')

----------  -------------------------

05:47:26    05:47:56

This is how we can use SQLite Time() function to get only time portion from given date and time value based on our requirements.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User