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.
In SQLite time() function is used to get only time from the given datetime string.
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 the TEXT as an output.
In the above SQLite date() syntax the 1st argument datetimestring will accept the values in following formats.
| Format | Example |
|---|---|
| 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 a fractional part |
The 2nd parameter modifiers (modifier1, modifier2, … etc.) will accept the values in following format
| Format | Example |
|---|---|
| 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 |
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 the current time by using the SQLite time() function. We can get the current time in 2 ways. By using only time() function without argument or we can also use the 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.