timestamps

Back to DuckDB Data Engineering Glossary

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:

Copy code
-- Create a timestamp
SELECT CAST('2023-05-01 14:30:00' AS TIMESTAMP);

-- Or using the shorthand syntax
SELECT '2023-05-01 14:30:00'::TIMESTAMP;

-- Get the current timestamp
SELECT NOW();

DuckDB also provides various functions for extracting parts of a timestamp or performing calculations:

Copy code
-- Extract the year from a timestamp
SELECT EXTRACT(YEAR FROM '2023-05-01 14:30:00'::TIMESTAMP);

-- Add one day to a timestamp
SELECT '2023-05-01 14:30:00'::TIMESTAMP + INTERVAL '1 day';

Understanding and effectively using timestamps is crucial for data analysts and engineers working with time-series data, event logs, or any application where precise timing information is important.