What is the usage of SQLite strftime function?

SQLite's strftime() function is a highly useful date and time function for formatting date and time values. It allows you to convert date and time values (such as timestamps) into strings with a specified format or extract specific components (such as year, month, day, and hour) from date and time values. This is highly convenient for working with time-related data in databases.

Formatting Date and Time

The basic syntax of the strftime() function is as follows:

sql
strftime(format, timestring, modifier, ...)

• format is a string that specifies the output format for the date and time.
• timestring is the input date and time string or column.
• modifier is optional and used to modify the time (e.g., 'start of month', '-1 day', etc.).

Example Usage

1. Get the current year

   sql
   SELECT strftime('%Y', 'now');

   This returns the current year, such as "2021".

2. Get the month and day of a specific date

   sql
   SELECT strftime('%m-%d', '2021-12-25');

   This returns "12-25".

3. Date calculation (e.g., get yesterday's date)

   sql
   SELECT strftime('%Y-%m-%d', 'now', '-1 day');

   If today is "2021-12-31", this returns "2021-12-30".

Real-World Applications

Suppose you have a database with an orders table that has an order_date column to store the order date. If you want to find all orders created in January 2021, you can use strftime() to format these dates and compare them:

sql
SELECT * FROM orders WHERE strftime('%Y-%m', order_date) = '2021-01';

This selects all orders where order_date is in January 2021.

This covers the basic uses and common application examples of the strftime() function in SQLite. This function is very powerful and highly useful when handling or formatting date and time values in SQL queries.