Here we will learn SQLite datetime() function with example and how to use SQLite datetime() function to get date and time from given string with example
In SQLite datetime() function is used to translate give string into date and time.
Following is the syntax of SQLite datetime() function to get date and time from the given string.
datetime(datetimestring, [modifier1, modifier2…, modifierN])
The SQLite datetime() function takes datetimestring & one or more modifier values and returns date and time in YYYY-MM-DD 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 the following format.
| 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 the 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 datetime() function with different examples.
Following is the example of sqlite datetime() function to return current date and time in YYYY-MM-DD HH:MM:SS format.
sqlite> SELECT datetime('now') as 'Current date & time';
Current date & time
-------------------
2016-08-31 06:38:14
In case if we want to get date and time preceded by 564 minutes from the 1st day of the current month we need to write SQLite query like as shown following.
sqlite> SELECT datetime('now','start of month','564 minutes') as 'Date and Time';
Date and Time
-------------------
2016-08-01 09:24:00
Now we will see how to use SQLite datetime() function with tables data in database for the create one table called Employee using following query.
CREATE TABLE Employee
(eid INTEGER PRIMARY KEY AUTOINCREMENT,
ename TEXT,
joining_date TEXT,
salary REAL);
Let’s insert some records to the Employee table using following statements.
INSERT INTO Employee(ename,joining_date,salary)
VALUES ('Rati',date('now','start of month'),5200),
('Umang',date('now','start of year'),5700),
('Prachi',date('now','start of year','-7 months'),5500),
('Ashmite',date('now','start of year','-32 days'),7000);
Now, see the records of Employee table with SQLite SELECT statement.
sqlite> SELECT * FROM Employee;
eid ename joining_date salary
---------- ---------- ------------ ----------
1 Rati 2016-08-01 5200.0
2 Umang 2016-01-01 5700.0
3 Prachi 2015-06-01 5500.0
4 Ashmite 2015-11-30 7000.0
Now write SQLite query like as shown following to get employees whose joining date more than 6 months and eligible for Appraisal cycle.
SELECT eid, ename, joining_date, salary, date(joining_date,'6 months') as 'Appraisal date' FROM Employee;
When we run above query we will get a result like as shown following.
eid ename joining_date salary Appraisal date
---------- ---------- ------------ ---------- --------------
1 Rati 2016-08-01 5200.0 2017-02-01
2 Umang 2016-01-01 5700.0 2016-07-01
3 Prachi 2015-06-01 5500.0 2015-12-01
4 Ashmite 2015-11-30 7000.0 2016-05-30
This is how we can use the SQLite datetime() function to get date and time from a given date and time string based on our requirements.