SQLite datetime

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

SQLite datetime() function

Description

The SQLite datetime() function returns "YYYY-MM-DD HH:MM:SS".

Syntax

  1. datetime(timestring, modifier, modifier, ...)  

Example-1 :

If you want to get the current date and time the following SQL can be used.

  1. SELECT datetime('now'as "Current Date and Time";  

Here is the result.

Current Date and Time
---------------------
2014-10-31 11:31:52

Example-2 :

If you want to get return the time portion from the given date-time value 2014-10-07 15:45:57.005678, the following SQL can be used.

  1. sqlite> SELECT time('2014-10-07 15:45:57.005678');  

Here is the result.

time('2014-10-07 15:45:57.005678')
----------------------------------
15:45:57

Example-3 :

If you want to get time preceded by 564 minutes form the 1st day of the current month, the following SQL can be used.

  1. SELECT datetime('now','start of month','564 minutes'as "Date and Time";  

Here is the result.

Date and Time
-------------------
2014-10-01 09:24:00

Example-4 :

Sample table : job_history

employee_id  start_date  end_date    job_id      department_id
-----------  ----------  ----------  ----------  -------------
102          1993-01-13  1998-07-24  IT_PROG     60
101          1989-09-21  1993-10-27  AC_ACCOUNT  110
101          1993-10-28  1997-03-15  AC_MGR      110
201          1996-02-17  1999-12-19  MK_REP      20
114          1998-03-24  1999-12-31  ST_CLERK    50
122          1999-01-01  1999-12-31  ST_CLERK    50
200          1987-09-17  1993-06-17  AD_ASST     90
176          1998-03-24  1998-12-31  SA_REP      80
176          1999-01-01  1999-12-31  SA_MAN      80
200          1994-07-01  1998-12-31  AC_ACCOUNT  90

 

If we want to find date of extension for all the employees getting 3 months more after their term end, the following SQL can be used.

  1. SELECT employee_id,job_id,department_id,start_date,end_date,  
  2. date(end_date,'3 months'as "Extension upto"   
  3. FROM job_history;  

Here is the result.

employee_id  job_id      department_id  start_date  end_date    Extension upto
-----------  ----------  -------------  ----------  ----------  --------------
102          IT_PROG     60             1993-01-13  1998-07-24  1998-10-24
101          AC_ACCOUNT  110            1989-09-21  1993-10-27  1994-01-27
101          AC_MGR      110            1993-10-28  1997-03-15  1997-06-15
201          MK_REP      20             1996-02-17  1999-12-19  2000-03-19
114          ST_CLERK    50             1998-03-24  1999-12-31  2000-03-31
122          ST_CLERK    50             1999-01-01  1999-12-31  2000-03-31
200          AD_ASST     90             1987-09-17  1993-06-17  1993-09-17
176          SA_REP      80             1998-03-24  1998-12-31  1999-03-31
176          SA_MAN      80             1999-01-01  1999-12-31  2000-03-31
200          AC_ACCOUNT  90             1994-07-01  1998-12-31  1999-03-31