SQLite strftime() function
Last update on August 19 2022 21:51:19 (UTC/GMT +8 hours)
Description
The SQLite strftime() function returns the date formatted according to the format string specified in argument first. The second parameter is used to mention the time string and followed by one or more modifiers can be used to get a different result.
Syntax:
strftime(format, timestring, modifier, modifier, ...)
Example-1:
If you want to extract the Year Month and Day for the current date, the following SQL can be used.
SELECT strftime('%Y %m %d','now');
Here is the result.
strftime('%Y %m %d','now')
--------------------------
2014 10 31
Here in the above example shows that the year, month and day part have been extracted from the current date in text format.
Example-2:
If you want to extract the Hour Minute Second and milliseconds from the current datetime, the following SQL can be used.
SELECT strftime('%H %M %S %s','now');
Here is the result.
strftime('%H %M %S %s','now')
-----------------------------
12 40 18 1414759218
Here in the above example shows that the year, month and day part have been extracted from the current date in text format.
Example-3:
Compute the number of seconds since a particular moment in 2014.
SELECT strftime('%s','now') - strftime('%s','2014-10-07 02:34:56');
Here is the result.
strftime('%s','now') - strftime('%s','2014-10-07 02:34:56')
-----------------------------------------------------------
2110042
Example-4:
Sample table: job_history
If we want to find out the year, month and day value of start_date from the table job_history, the following SQL can be used.
SELECT start_date,strftime('%Y',start_date) as "Year",
strftime('%m',start_date) as "Month",
strftime('%d',start_date) as "Day"
FROM job_history;
Here is the result.
start_date Year Month Day
---------- ---------- ---------- ------
1993-01-13 1993 01 13
1989-09-21 1989 09 21
1993-10-28 1993 10 28
1996-02-17 1996 02 17
1998-03-24 1998 03 24
1999-01-01 1999 01 01
1987-09-17 1987 09 17
1998-03-24 1998 03 24
1999-01-01 1999 01 01
1994-07-01 1994 07 01
Example-5:
Sample table: job_history
If we want to find out the year, month and day value of start_date for those employees who joined in 1 quarter from the table job_history, the following SQL can be used.
SELECT start_date,strftime('%Y',start_date) as "Year",
strftime('%m',start_date) as "Month",
strftime('%d',start_date) as "Day"
FROM job_history
WHERE strftime('%m',start_date)
IN('01','02','03');
Here is the result.
start_date Year Month Day
---------- ---------- ---------- ------
1993-01-13 1993 01 13
1996-02-17 1996 02 17
1998-03-24 1998 03 24
1999-01-01 1999 01 01
1998-03-24 1998 03 24
1999-01-01 1999 01 01
Previous:
JULIANDAY
Next:
Create, Drop views
