What is Date & Time

Introduction

Working with dates and times is one of the most essential skills in SQL. Almost every database contains temporal data - order timestamps, birth dates, subscription periods, event schedules, and more.

In this tutorial, you will learn:

What date and time data represents in databases
Common date/time data types in SQL
Why temporal data requires special handling
The building blocks of date and time values

What is Date & Time Data?

Date and time data represents specific points in time or durations. Unlike simple numbers or text, temporal data has unique properties:

Date Components

A date consists of:

Year - The calendar year (e.g., 2024)
Month - The month of the year (1-12)
Day - The day of the month (1-31)

Time Components

A time consists of:

Hour - The hour of the day (0-23 or 1-12)
Minute - Minutes past the hour (0-59)
Second - Seconds past the minute (0-59)
Milliseconds - Fractions of a second (optional)

DateTime: The Complete Picture

A datetime or timestamp combines both:

Generated visual representation from text diagram for What is Date & Time Data?.

Exploded view of a datetime value showing each component (year, month, day, hour, minute, second) labeled and color-coded

Common Date/Time Data Types

Different database systems offer various date/time data types:

Standard SQL Date/Time Types

Data Type	Description	Example
`DATE`	Date only (no time)	`2024-03-15`
`TIME`	Time only (no date)	`14:30:45`
`DATETIME`	Date and time combined	`2024-03-15 14:30:45`
`TIMESTAMP`	Date/time with timezone awareness	`2024-03-15 14:30:45+00`

SQLite Date/Time Storage

SQLite doesn't have dedicated date/time types. Instead, dates are stored as:

TEXT - ISO 8601 format strings: '2024-03-15 14:30:45'
REAL - Julian day numbers (days since Nov 24, 4714 BC)
INTEGER - Unix timestamps (seconds since 1970-01-01)

Most commonly, SQLite uses text in ISO format:

'2024-03-15'           -- Date only
'14:30:45'             -- Time only
'2024-03-15 14:30:45'  -- DateTime

Why Date/Time Needs Special Handling

Date and time data is more complex than regular numbers because:

1. Irregular Units

Unlike the decimal system, time units aren't uniform:

Months have 28, 29, 30, or 31 days
Years have 365 or 366 days (leap years)
Hours use base 24, minutes use base 60

2. Calendar Complexity

   February 2024        February 2023
Su Mo Tu We Th Fr Sa   Su Mo Tu We Th Fr Sa
             1  2  3             1  2  3  4
 4  5  6  7  8  9 10    5  6  7  8  9 10 11
11 12 13 14 15 16 17   12 13 14 15 16 17 18
18 19 20 21 22 23 24   19 20 21 22 23 24 25
25 26 27 28 29         26 27 28

2024 is a leap year (29 days in February), 2023 is not (28 days).

3. Time Zone Considerations

The same moment in time has different representations:

New York: 2024-03-15 10:00:00 EST
London: 2024-03-15 15:00:00 GMT
Tokyo: 2024-03-16 00:00:00 JST

4. Date Arithmetic

Adding to dates isn't straightforward:

January 31 + 1 month = ? (February 28/29 or March 1?)
March 31 - 1 month = ? (February 28/29?)

SQL functions handle these complexities for you!

Date/Time in Our E-Commerce Database

Our Brazilian E-Commerce dataset contains several date/time columns:

-- Explore date/time columns in the orders table
SELECT 
    order_id,
    order_purchase_timestamp,
    order_approved_at,
    order_delivered_carrier_date,
    order_delivered_customer_date,
    order_estimated_delivery_date
FROM olist_orders_dataset
LIMIT 5;

Key Date Columns

Column	Description	Format
`order_purchase_timestamp`	When customer placed the order	DateTime
`order_approved_at`	When payment was approved	DateTime
`order_delivered_carrier_date`	When shipped to carrier	DateTime
`order_delivered_customer_date`	When customer received order	DateTime
`order_estimated_delivery_date`	Expected delivery date	Date

These columns allow us to analyze:

Order patterns by day, month, or year
Delivery performance and delays
Time between order and delivery
Seasonal trends

Date Formats and Standards

Dates can be written in many formats, which can cause confusion:

Common Date Formats

Format	Example	Used In
ISO 8601	`2024-03-15`	International standard, SQL
US Format	`03/15/2024`	United States
European	`15/03/2024`	Europe, Brazil
Long Form	`March 15, 2024`	Documents

ISO 8601: The SQL Standard

SQL databases typically use ISO 8601 format:

YYYY-MM-DD HH:MM:SS
2024-03-15 14:30:45

Why ISO 8601?

Unambiguous: No confusion between month and day
Sortable: Alphabetical order = chronological order
International: Works across all locales

Best Practice

Always use ISO format when working with dates in SQL:

-- Good: ISO format
WHERE order_date = '2024-03-15'

-- Avoid: Ambiguous format
WHERE order_date = '03/15/2024'

Practice: Exploring Date Data

Let's explore the date/time data in our orders table:

-- Explore date range in our dataset
SELECT 
    MIN(order_purchase_timestamp) AS earliest_order,
    MAX(order_purchase_timestamp) AS latest_order,
    COUNT(*) AS total_orders
FROM olist_orders_dataset;

This query shows you the time span covered by our dataset - essential information before doing any date-based analysis!

Summary

Key Takeaways

Date/Time Components
- Dates have year, month, and day
- Times have hour, minute, second, and optional milliseconds
- DateTime combines both
Storage in SQLite
- Dates are typically stored as TEXT in ISO format
- Format: YYYY-MM-DD HH:MM:SS
Why Special Functions?
- Irregular units (28-31 days/month, 365-366 days/year)
- Calendar complexity (leap years)
- Time zone handling
- Date arithmetic
Best Practices
- Use ISO 8601 format (YYYY-MM-DD)
- Store timestamps consistently
- Be aware of time zones in your data

What's Next?

Now that you understand what date/time data is, the next tutorials will teach you specific functions to:

Extract parts of dates (year, month, day)
Format dates for display
Calculate differences between dates
Add or subtract time periods