SQLite strftime() Function

Summary: in this tutorial, you have learned how to use the SQLite strftime() function to format a datetime value based on a specific format.

Introduction to the SQLite strftime() function #

The strftime() function is used to format a time, a date, or a datetime value based on a specified format.

Here’s the syntax of the strftime() function:

strftime(format_string, time_string [, modifier, ...])

In this syntax:

format_string specifies the format for the datetime value specified by the time_string value.
time_string specifies the date and time value. For example, the now time string returns the current date and time in UTC.
Each modifier applies a transformation to the date time value on its left. The order of transformation is from left to right. The strftime() can take one or more modifiers. Here’s the complete list of modifiers.

Note that all other date and time functions such as date(), time(), datetime(), and julianday() can be expressed using the strftime() function:

Function	Equivalent strftime()
`date()`	strftime(‘%Y-%m-%d’, …)
`time()`	strftime(‘%H:%M:%S’, …)
`datetime()`	strftime(‘%Y-%m-%d %H:%M:%S’, …)
`julianday()`	strftime(‘%J’, …)

The following table shows a completed list of string formats:

Format	Description
%d	day of the month: 01-31
%e	day of the month without leading zero like 1, 2, 3, … 31
%f	fractional seconds: SS.SSS
%H	hour: 00-24
%I	hour for 12-hour clock: 01-12
%k	hour without leading zero: 1, 2 … 24
%j	day of the year: 001-366
%J	Julian day number
%m	month: 01-12
%M	minute: 00-59
%s	seconds since 1970-01-01
%S	seconds: 00-59
%w	day of week 0-6 with Sunday==0
%W	week of the year: 00-53
%Y	year: 0000-9999
%%	%
%p	AM or PM
%P	am or pm

SQLite strftime() function examples #

Let’s take some examples of using the strftime() function.

1) Getting the current time as a unix timestamp #

The following example uses the strftime() function to return the current time as a Unix timestamp:

SELECT strftime('%s','now');

Output:

result
----------
1712841001

2) Using the strftime() function to extract days from dates #

The following example uses the strftime() function to extract the day from a date:

SELECT strftime('%d','2018-10-11') result;

Output:

result
------
11

3) Using the strftime() function to extract months from dates #

The following example uses the strftime() function to extract the month from a date:

SELECT strftime('%m','2018-10-11');

Output:

result
------
10

4) Using the strftime() function to extract months from dates #

The following example uses the strftime() function to extract a year from a date:

SELECT strftime('%Y','2018-10-11') result;

Output:

result
------
2018

5) Using the strftime() function to extract hours from times #

The following example uses the strftime() function to extract the hour from a time:

SELECT strftime('%H','10:20:30') result;

Output:

result
------
10

6) Using the strftime() function to extract minutes from times #

The following example uses the strftime() function to extract the minute from a time:

SELECT strftime('%M','10:20:30') result;

Output:

result
------
20

7) Using the strftime() function to extract seconds from times #

The following example uses the strftime() function to extract a second from a time:

SELECT strftime('%S','10:20:30') result;

Output:

result
------
30

8) Using the strftime() function to extract jullian days from datetime values #

The following example uses the strftime() function to extract the Julian day from a datetime:

SELECT strftime('%J','2018-10-11 10:20:30') result;

Output:

result
-----------------
2458402.930902777

9) Using the strftime() function to extract a day of the year from a date #

The following example uses the strftime() function to extract the day of the year from a date:

SELECT strftime('%j','2018-10-11') result;

Output:

result
------
284

10) Using the strftime() function to extract a day of the week from a date #

The following statement uses the strftime() function to extract the day of the week from a date:

SELECT strftime('%w','2018-10-11') result;

Output:

result
------
4

11) Using the strftime() function to extract the week of the year from a date #

The following statement uses the strftime() function to extract the week of the year from a date:

SELECT strftime('%W','2018-10-11') result;

Output:

result
------
41

12) Using the strftime() function to extract the fractional seconds from a time #

The following statement uses the strftime() function to extract the fractional seconds from a time:

SELECT strftime('%f','2018-10-11 10:20:30.999') result;

Output:

result
------
30.999

13) Using the strftime() function with table data #

First, create a table called employees to store employee data:

CREATE TABLE employees(
    id INTEGER PRIMARY KEY NOT NULL,
    name VARCHAR(255) NOT NULL,
    joined_date TEXT NOT NULL
);

Second, insert some rows into the employees table:

INSERT INTO employees(name, joined_date)
VALUES
   ('John Doe', '2018-01-05'),
   ('Jane Doe', '2019-02-10');

Third, retrieve data from the employees table:

SELECT * FROM employees;

Output:

id | name     | joined_date
---+----------+------------
1  | John Doe | 2018-01-05
2  | Jane Doe | 2018-02-10
(2 rows)

Finally, calculate the years of service for all the employees to 11 April 2024:

SELECT
  id,
  name,
  strftime ('%Y', '2024-04-11') - strftime ('%Y', joined_date) AS years_of_service
FROM
  employees;

Output:

id | name     | years_of_service
---+----------+-----------------
1  | John Doe | 6
2  | Jane Doe | 5
(2 rows)

Summary #

Use the strftime() function to format a time, a date, or a datetime based on a specified format.

Was this tutorial helpful?