How to Use Strftime() Function in SQLite?

The strftime() function is a built-in function of SQLite that is used to format date and time values. The output of the strftime() function is a string that represents the desired format of date and time. This function is mainly used for generating human-readable date and time formats from a date-time value.

For example, if you have a timestamp in your database, you can use strftime() to retrieve specific elements of that timestamp such as year, month, day, hour, minute, and second.

Syntax of the strftime() Function

The syntax of the strftime() function is straightforward and easy to use. The general format of the strftime() function is as follows:

strftime(format,time)

Where format is the desired format of the output and time is the timestamp value that you want to convert. The strftime() method takes a string format input that tells it how to format the date and time data.

Format Codes of the strftime() Function

The format argument of the strftime() function may contain format codes that represent different date and time elements. Here are a few format codes that are commonly used in strftime():

%Y: represents the year in four digits.
%m: represents the month in two digits.
%d: represents the day in two digits.
%H: represents the hour in 24-hour format.
%M: represents the minute in two digits.
%S: represents the seconds in two digits.
%w: represents the weekday as a decimal number.

How to Use strftime() Function in SQLite?

The strftime() function is often used in SQLite for generating date and time formats that are compatible with other software or applications. For example, if you have a database that stores timestamps in Unix time format, you can use strftime() to convert them into a readable format.

Similarly, if you want to retrieve all the records that were added on a particular date or between two dates, you can use strftime() to generate the required query.

Here are a few examples of how strftime() can be used in SQLite:

Format a Date
Retrieve the Year of a Timestamp
Retrieve the Day of the Week of a Timestamp
Retrieve the Date and Time in Another Format
Calculate the Difference in Years

1: Format a Date

You can use strftime() to modify the date as DD-MM-YYYY:

SELECT strftime('%d-%m-%Y', '2023-11-10') AS formatted_date;

2: Retrieve the Year of a Timestamp

You can use strftime() to retrieve the year of a timestamp:

SELECT strftime('%Y', '2023-11-10') AS year;

3: Retrieve the Day of the week of a Timestamp

You can also use strftime() function to retrieve the day of the week of a timestamp:

SELECT strftime('%w', '2023-11-10') AS date;

4: Retrieve the Date and Time in Another Format

You can use strftime() function to retrieve the date and time in another format:

SELECT strftime('%d/%m/%Y %H:%M', '2022-06-10');

5: Calculate the Difference in Years

The strftime() can also be used to determine how many years are between two specified dates. Consider the following example:

SELECT strftime('%Y', '2023-10-01') - strftime('%Y', '1990-09-29') AS number_of_years;

Conclusion

The strftime() is a highly useful function that provides an easy way to retrieve and format date and time values in SQLite. It can be used for a variety of purposes, such as generating reports or analyzing trends, and can be customized to provide different date and time formats depending on the requirements.