Handling Time Series Data in SQL - Date and Time Functions

Introduction

Date and time functions help you work with temporal data in SQL. They let you extract parts of dates, calculate differences, and format date/time values.

Why Date/Time Functions Matter?

They’re essential when you need to:

• Calculate time differences
• Extract specific parts of dates
• Group data by time periods
• Format dates for display

Main Content: Understanding Date/Time Functions

Let’s use this simple table:

-- Create a simple table
CREATE TABLE events (
    event_name VARCHAR(50),
    event_datetime TIMESTAMP
);

-- Insert sample data
INSERT INTO events VALUES
('Meeting A', '2024-01-15 09:30:00'),
('Meeting B', '2024-01-15 14:45:00'),
('Meeting C', '2024-01-16 10:00:00'),
('Meeting D', '2024-01-17 11:30:00'),
('Meeting E', '2024-01-17 15:15:00');

Example 1: Extracting Date Parts

Let’s extract different parts of the datetime:

SELECT 
    event_name,
    event_datetime,
    EXTRACT(YEAR FROM event_datetime) as year,
    EXTRACT(MONTH FROM event_datetime) as month,
    EXTRACT(DAY FROM event_datetime) as day,
    EXTRACT(HOUR FROM event_datetime) as hour,
    EXTRACT(MINUTE FROM event_datetime) as minute
FROM events;

This query shows:

1. Full datetime
2. Individual components
3. Each part as a number

Results:

event_name | event_datetime        | year | month | day | hour | minute
-----------|----------------------|------|-------|-----|------|--------
Meeting A  | 2024-01-15 09:30:00 | 2024 | 1     | 15  | 9    | 30
Meeting B  | 2024-01-15 14:45:00 | 2024 | 1     | 15  | 14   | 45
Meeting C  | 2024-01-16 10:00:00 | 2024 | 1     | 16  | 10   | 0
Meeting D  | 2024-01-17 11:30:00 | 2024 | 1     | 17  | 11   | 30
Meeting E  | 2024-01-17 15:15:00 | 2024 | 1     | 17  | 15   | 15

Example 2: Date Arithmetic

Let’s calculate time differences:

SELECT 
    event_name,
    event_datetime,
    event_datetime + INTERVAL '1 day' as next_day,
    event_datetime - INTERVAL '1 hour' as hour_before,
    event_datetime + INTERVAL '30 minutes' as thirty_min_later
FROM events;

This query shows:

1. Original datetime
2. One day later
3. One hour before
4. 30 minutes later

Results:

event_name | event_datetime        | next_day             | hour_before          | thirty_min_later
-----------|----------------------|---------------------|--------------------|-----------------------
Meeting A  | 2024-01-15 09:30:00 | 2024-01-16 09:30:00 | 2024-01-15 08:30:00 | 2024-01-15 10:00:00
Meeting B  | 2024-01-15 14:45:00 | 2024-01-16 14:45:00 | 2024-01-15 13:45:00 | 2024-01-15 15:15:00
Meeting C  | 2024-01-16 10:00:00 | 2024-01-17 10:00:00 | 2024-01-16 09:00:00 | 2024-01-16 10:30:00
Meeting D  | 2024-01-17 11:30:00 | 2024-01-18 11:30:00 | 2024-01-17 10:30:00 | 2024-01-17 12:00:00
Meeting E  | 2024-01-17 15:15:00 | 2024-01-18 15:15:00 | 2024-01-17 14:15:00 | 2024-01-17 15:45:00

Example 3: Date Formatting

Let’s format dates in different ways:

SELECT 
    event_name,
    event_datetime,
    TO_CHAR(event_datetime, 'Day, Month DD, YYYY') as full_date,
    TO_CHAR(event_datetime, 'HH24:MI') as time_24hr,
    TO_CHAR(event_datetime, 'HH:MI AM') as time_12hr
FROM events;

This query shows:

1. Original datetime
2. Formatted full date
3. 24-hour time
4. 12-hour time

Results:

event_name | event_datetime        | full_date              | time_24hr | time_12hr
-----------|----------------------|------------------------|-----------|------------
Meeting A  | 2024-01-15 09:30:00 | Monday, January 15, 2024| 09:30     | 09:30 AM
Meeting B  | 2024-01-15 14:45:00 | Monday, January 15, 2024| 14:45     | 02:45 PM
Meeting C  | 2024-01-16 10:00:00 | Tuesday, January 16, 2024| 10:00    | 10:00 AM
Meeting D  | 2024-01-17 11:30:00 | Wednesday, January 17, 2024| 11:30  | 11:30 AM
Meeting E  | 2024-01-17 15:15:00 | Wednesday, January 17, 2024| 15:15  | 03:15 PM

Best Practices

1. Date Storage

   • Use proper date/time data types
   • Store in UTC when possible
   • Keep consistent formats

2. Date Arithmetic

   • Use INTERVAL for time math
   • Consider timezone effects
   • Handle month/year boundaries carefully

3. Date Formatting

   • Use TO_CHAR for display
   • Keep formats consistent
   • Document format patterns

4. Common Uses

   • Event scheduling
   • Duration calculations
   • Report formatting
   • Time-based grouping

5. Things to Remember

   • Dates are stored internally as numbers
   • Timezone matters
   • Format strings are case-sensitive
   • EXTRACT returns numbers

Remember:

• Use proper date types
• Be careful with timezones
• Format dates for display
• Use INTERVAL for calculations

Common Mistakes to Avoid:

1. Storing dates as text
2. Ignoring timezones
3. Incorrect format patterns
4. Manual date arithmetic

Happy querying! 🚀