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, ...])Code language: SQL (Structured Query Language) (sql)

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:

The following table shows a completed list of string formats:

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');Code language: SQL (Structured Query Language) (sql)

Output:

result
----------
1712841001Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
11Code language: SQL (Structured Query Language) (sql)

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');Code language: SQL (Structured Query Language) (sql)

Output:

result
------
10Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
2018Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
10Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
20Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
30Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

result
-----------------
2458402.930902777Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
284Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
4Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
41Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
30.999Code language: SQL (Structured Query Language) (sql)

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
);Code language: SQL (Structured Query Language) (sql)

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');Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the employees table:

SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)

Output:

id | name     | joined_date
---+----------+------------
1  | John Doe | 2018-01-05
2  | Jane Doe | 2018-02-10
(2 rows)Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

id | name     | years_of_service
---+----------+-----------------
1  | John Doe | 6
2  | Jane Doe | 5
(2 rows)Code language: SQL (Structured Query Language) (sql)

Summary

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