SQLite Date & Time

SQLite supports a variety of date and time functions that allow you to work with date and time values in your database. These functions enable you to retrieve and manipulate dates, times, and timestamps, as well as perform calculations and comparisons with these values.

Syntax

The basic syntax for working with date and time values in SQLite is as follows:

SELECT strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime');

The %Y-%m-%d %H:%M:%S format string is used to format the date and time value. The now keyword is used to indicate the current date and time, and the localtime keyword is used to adjust for the local time zone.

Example

Suppose we have a table called events that contains events with start and end times. We can use the strftime function to format these times as needed.

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  name TEXT,
  start_time TEXT,
  end_time TEXT
);

INSERT INTO events (id, name, start_time, end_time)
VALUES (1, 'Meeting', '2021-10-01 14:00:00', '2021-10-01 15:30:00');

SELECT id, name, strftime('%Y-%m-%d %H:%M:%S', start_time) AS formatted_start_time,
strftime('%Y-%m-%d %H:%M:%S', end_time) AS formatted_end_time
FROM events;

Output

The output of the SELECT statement would be:

id | name    | formatted_start_time   | formatted_end_time
---+---------+-----------------------+-----------------------
 1 | Meeting | 2021-10-01 14:00:00    | 2021-10-01 15:30:00

Explanation

In the example above, we first create a table called events with columns for event ID, name, start time, and end time. We then insert a sample event into the table.

We use the strftime function in the SELECT statement to format the start and end times as needed. We use the %Y-%m-%d %H:%M:%S format string to format the times as year-month-day hour:minute:second.

The output of the SELECT statement shows the formatted start and end times for the event.

Use

SQLite date and time functions are useful in scenarios where you need to work with date and time data in your database. They can be used to format dates and times, perform calculations and comparisons with date and time values, and extract parts of date and time values.

Important Points

• SQLite supports a variety of date and time functions, such as strftime, date, time, and datetime.
• The strftime function can be used to format dates and times.
• Dates and times can be compared using standard comparison operators such as <, >, <=, and >=.
• Date and time functions can be used in SQL statements such as SELECT, UPDATE, and DELETE.
• Be mindful of time zones when working with dates and times.

Summary

In this tutorial, we learned about working with date and time values in SQLite. We saw how to format date and time values using the strftime function and how to work with these values in SQL statements such as SELECT. We also discussed some important points to keep in mind when working with date and time values in SQLite.