When working with data that involves dates and times, SQL provides specialized data types to accurately capture and store this information. These include the DATE, TIME, TIMESTAMP, and INTERVAL types. Each serves a distinct purpose, helping you model real-world scenarios such as tracking birthdays, scheduling event times, or measuring durations.

The DATE type is ideal for storing calendar dates such as a person's birthday: 1990-05-15. The TIME type is used for storing times independent of any particular day, such as 14:30:00 for an event that starts at 2:30 PM. The TIMESTAMP type combines both date and time, making it perfect for recording moments like when a record was created or an event occurred, for example, 2024-06-01 09:15:30. The INTERVAL type is used to represent a span of time, such as 3 days or 2 hours, which is useful when you need to calculate differences or durations, like the length of an event or the time between two activities.

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,
    event_time TIME,
    created_at TIMESTAMP
);

In the table definition above, you see how each date and time type fits a specific role. The event_date column uses the DATE type to store only the date of the event, such as a concert happening on 2024-07-10. The event_time column uses the TIME type to store the specific time the event starts, such as 19:30:00. The created_at column uses the TIMESTAMP type, which captures both the date and the exact time when the event record was created, like 2024-06-01 09:15:30.

The DATE type stores year, month, and day, making it suitable for any scenario where only the calendar date matters. The TIME type records hours, minutes, and seconds, best for activities where only the time of day is important. The TIMESTAMP type combines both date and time, so you should use it whenever you need to track a precise moment. The INTERVAL type does not store a specific date or time, but rather a duration, such as 5 days or 10 minutes, which is useful for calculations or scheduling.

INSERT INTO events (id, event_name, event_date, event_time, created_at) VALUES
(1, 'Morning Yoga', '2024-07-10', '07:00:00', '2024-06-01 09:15:30'),
(2, 'Conference Call', '2024-07-12', '14:30:00', '2024-06-02 11:05:00'),
(3, 'Birthday Party', '2024-08-01', '18:00:00', '2024-06-03 16:45:12');