timestamps

Q: How do you create a timestamp in DuckDB?

Cast a string with CAST('2023-05-01 14:30:00' AS TIMESTAMP) or the shorthand '2023-05-01 14:30:00'::TIMESTAMP , and get the current time with NOW() .

Q: How do you extract parts of a timestamp in DuckDB?

Use EXTRACT(YEAR FROM ts) (or MONTH, DAY, etc.), and do date math with intervals, for example ts + INTERVAL '1 day' .

Timestamps are a fundamental data type in databases and programming languages that represent a specific point in time, typically including both the date and…

Timestamps are a fundamental data type in databases and programming languages that represent a specific point in time, typically including both the date and time down to a precise level such as seconds or milliseconds. In DuckDB, timestamps can be stored and manipulated using the TIMESTAMP data type. They are particularly useful for tracking when events occur, logging changes, or performing time-based analytics.

To work with timestamps in DuckDB, you can use functions like NOW() to get the current timestamp, or create timestamps from strings using CAST or the :: operator. For example:

Related terms

data type →

In the context of databases and programming, a data type defines the nature of data that can be stored in a specific field or variable.

DATE_TRUNC →

DATE_TRUNC(unit, value) rounds a date or timestamp down to the start of a specified unit — such as day, month, or year — commonly used to bucket time-series data.

EXTRACT →

EXTRACT(field FROM value) pulls a single numeric component — such as year, month, or hour — out of a date, time, or timestamp value.

INTERVAL →

INTERVAL is a SQL data type and literal syntax representing a span of time — such as '3 days' or '1 month' — used for date/timestamp arithmetic.

CAST →

CAST converts a value from one data type to another, such as turning a string into an integer or a timestamp into a date.

time-series →

A time series is a sequence of data points ordered chronologically. Time-series analysis uncovers trends, seasonality, and forecasts in data like metrics, sensor readings, and financial ticks — and DuckDB handles most of these patterns in plain SQL.

FAQS

A timestamp is a data type representing a specific point in time, typically date plus time down to seconds or milliseconds. It's used for tracking events, logging changes, and time-based analytics.

Cast a string with CAST('2023-05-01 14:30:00' AS TIMESTAMP) or the shorthand '2023-05-01 14:30:00'::TIMESTAMP, and get the current time with NOW().

Use EXTRACT(YEAR FROM ts) (or MONTH, DAY, etc.), and do date math with intervals, for example ts + INTERVAL '1 day'.