21.7. Time Functions

21.7. Time Functions
Prev	Up	Chapter 21. Functions	Home	Next

21.7. Time Functions #

time_bucket(bucket_width INTERVAL, timestamp_col TIMESTAMP, origin TIMESTAMP) returns TIMESTAMP #

Buckets timestamps into time intervals for time-series analysis. This function is compatible with the TimescaleDB time_bucket function, allowing for easier migration and interoperability.

Example 21.22.

-- Group events by hour
SELECT time_bucket(INTERVAL '1 hour', created_at) as hour_bucket, COUNT(*)
FROM events
GROUP BY hour_bucket
ORDER BY hour_bucket;

-- Group by 15-minute intervals
SELECT time_bucket(INTERVAL '15 minutes', timestamp_col), AVG(value)
FROM sensor_data
WHERE timestamp_col >= '2024-01-01'
GROUP BY 1
ORDER BY 1;

Required parameters:

Name	Type	Description
`bucket_width`	`interval`	The time interval for bucketing (e.g., `'1 hour'`, `'15 minutes'`)
`timestamp_col`	`timestamp`	The timestamp column to bucket

Optional parameters:

Name	Type	Description
`origin`	`timestamp`	The origin point for bucketing. Buckets are aligned to this timestamp.

strftime (timestamp_expr, format_string) returns TEXT #

Formats timestamps as strings using standard format codes. This function provides flexible timestamp formatting for display and export purposes.

Example 21.23.

-- Format current timestamp
SELECT strftime(NOW(), '%Y-%m-%d %H:%M:%S') AS formatted_time;

-- Format timestamps in different formats
SELECT
    order_id,
    strftime(created_at, '%Y-%m-%d') AS order_date,
    strftime(created_at, '%H:%M') AS order_time,
    strftime(created_at, '%A, %B %d, %Y') AS readable_date
FROM orders;

-- Use for partitioning file exports
COPY (SELECT * FROM events WHERE event_date = '2024-01-01')
TO 's3://bucket/events/' || strftime('2024-01-01'::timestamp, '%Y/%m/%d') || '/events.parquet';

Common format codes:

%Y: 4-digit year (2024).
%m: Month as number (01-12).
%d: Day of month (01-31).
%H: Hour (00-23).
%M: Minute (00-59).
%S: Second (00-59).
%A: Full weekday name (Monday).
%B: Full month name (January).

Optional parameters:

Name	Type	Description
`timestamp_expr`	`timestamp`	The timestamp value to format
`format_string`	`text`	The format string with format codes

strptime(string_expr, format_string) returns TIMESTAMP #

Parses strings into timestamps using format codes.

Example 21.24.

-- Parse date strings
SELECT strptime('2024-01-15 14:30:00', '%Y-%m-%d %H:%M:%S') AS parsed_timestamp;

-- Parse different formats
SELECT
    strptime('Jan 15, 2024', '%b %d, %Y') AS date1,
    strptime('15/01/2024', '%d/%m/%Y') AS date2,
    strptime('2024-01-15T14:30:00Z', '%Y-%m-%dT%H:%M:%SZ') AS iso_date;

-- Parse log timestamps
SELECT
    log_id,
    strptime(timestamp_string, '%Y-%m-%d %H:%M:%S') AS parsed_time,
    message
FROM raw_logs;

Required parameters:

Name	Type	Description
`string_expr`	`text`	The string to parse as a timestamp
`format_string`	`text`	The format string describing the input format

epoch(timestamp_expr) returns BIGINT #

Converts timestamps to Unix epoch seconds (seconds since 1970-01-01 00:00:00 UTC).

Example 21.25.

-- Get current epoch time
SELECT epoch(NOW()) AS current_epoch;

-- Convert timestamps for API usage
SELECT
    event_id,
    epoch(event_timestamp) AS epoch_seconds
FROM events;

-- Filter using epoch time
SELECT * FROM events
WHERE epoch(created_at) > 1640995200; -- After 2022-01-01

Required parameters:

Name	Type	Description
`map_col`	`duckdb.map`	The map from which to extract parameters
`key`	`duckdb.unresolved_type`	The key to find in the map

epoch_ms(timestamp_expr) returns BIGINT #

Converts timestamps to Unix epoch milliseconds.

Example 21.26.

-- High-precision timestamp for JavaScript
SELECT epoch_ms(NOW()) AS timestamp_ms;

-- For time-series data
SELECT
    sensor_id,
    epoch_ms(reading_time) AS timestamp_ms,
    value
FROM sensor_readings;

Required parameters:

Name	Type	Description
`timestamp_expr`	`timestamp`	The timestamp to convert to Unix epoch milliseconds

epoch_ms(milliseconds) returns TIMESTAMP #

Converts Unix epoch milliseconds to a timestamp.

Example 21.27.

-- Convert epoch milliseconds to timestamp
SELECT epoch_ms(1640995200000) AS timestamp_from_ms; -- 2022-01-01 00:00:00

-- Convert stored milliseconds back to timestamps
SELECT
    event_id,
    epoch_ms(timestamp_ms) AS event_time
FROM events;

Required parameters:

Name	Type	Description
`milliseconds`	`bigint`	Milliseconds since the Unix epoch

epoch_us(timestamp_expr) returns BIGINT #

Converts timestamps to Unix epoch microseconds.

Example 21.28.

-- Microsecond precision timestamps
SELECT epoch_us(NOW()) AS timestamp_us;

Required parameters:

Name	Type	Description
`timestamp_expr`	`timestamp`	The timestamp to convert to Unix epoch microseconds

epoch_ns(timestamp_expr) returns BIGINT #

Converts timestamps to Unix epoch nanoseconds.

Example 21.29.

-- Nanosecond precision timestamps
SELECT epoch_ns(NOW()) AS timestamp_ns;

Required parameters:

Name	Type	Description
`timestamp_expr`	`timestamp`	The timestamp to convert to Unix epoch nanoseconds

make_timestamp(microseconds) returns TIMESTAMP #

Creates a timestamp from microseconds since the Unix epoch (1970-01-01 00:00:00 UTC).

Example 21.30.

-- Create timestamp from current epoch microseconds
SELECT make_timestamp(epoch_us(NOW())) AS reconstructed_timestamp;

-- Create specific timestamps
SELECT make_timestamp(1640995200000000) AS new_years_2022; -- 2022-01-01 00:00:00

Required parameters:

Name	Type	Description
`microseconds`	`BIGINT`	Microseconds since the Unix epoch

make_timestamptz(microseconds) returns TIMESTAMPTZ #

Creates a timestamp with timezone from microseconds since the Unix epoch.

Example 21.31.

-- Create timestamptz from current epoch microseconds
SELECT make_timestamptz(epoch_us(NOW())) AS reconstructed_timestamptz;

-- Create specific timestamptz
SELECT make_timestamptz(1640995200000000) AS new_years_2022_tz;

Required parameters:

Name	Type	Description
`microseconds`	`bigint`	Microseconds since the Unix epoch

Prev	Up	Next
21.6. Sampling Functions	Home	21.8. DuckDB Administration Functions