DuckDB provides us with a bunch of epoch...() functions that enable us to get the Unix epoch time from a given date/time value. Different functions return their result using different units (for example seconds, milliseconds, etc). The epoch_us() function returns its result in microseconds.
Unix epoch time is typically expressed as the number of seconds that have elapsed since January 1, 1970 (UTC), but epoch_us() function returns the equivalent amount in microseconds.
In DuckDB, the epoch_ms() function serves a dual purpose. It converts timestamp values into Unix epoch time in milliseconds and also performs the reverse operation, transforming Unix epoch time values back into timestamps.
Unix epoch time is typically expressed as the number of seconds that have elapsed since January 1, 1970 (UTC), but this function returns the equivalent amount in milliseconds.
The function is similar to the epoch() function, which returns its result in seconds. However, the epoch() function only works in one direction; it converts a timestamp value to epoch time, but it doesn’t work the other way around like epoch_ms() can.
Date/time arithmetic, such as adding one or more minutes to a date/time value, is an operation we often need to perform when using SQL databases such as DuckDB. As with most RDBMSs, DuckDB makes this easy for us to achieve.
Here are two options for adding one or more minutes to a date, timestamp, or time value.
DuckDB’s epoch() function is a useful time conversion tool that allows us to transform timestamp values into Unix epoch time – the number of seconds that have elapsed since January 1, 1970 (UTC).
The function is particularly useful when working with time-series data and when we need to perform mathematical operations on timestamp values. By converting timestamps to integer representations, we can easily calculate time differences, group time-based data, or integrate with systems that use epoch time.
In the ISO-8601 calendar, years begin on the first Monday closest to January 1, meaning the start date can fall between December 29 and January 4. This differs from the Gregorian calendar, where years always start on January 1, leading to potential discrepancies around these dates. Additionally, ISO-8601 years, or just ISO years, can be either 52 or 53 weeks long, depending on their starting point.
This article provides four options for extracting the ISO year from a date in DuckDB, accounting for these unique calendar rules.
DuckDB provides us with the means to add and subtract intervals to/from date, timestamp, and time values. We can perform subtraction with the minus (-) operator or the date_add() function (along with the minus operator).
Below are examples of using each method to subtract minutes from date/time values.
Time-based operations are a fundamental aspect of data analysis and manipulation. DuckDB, the in-process analytical database management system, provides robust support for handling time-based operations through its INTERVAL data type.
This article explores the capabilities, syntax, and practical applications of intervals in DuckDB.
DuckDB provides us with a good selection of functions for working with date/time values. Among them is date_part(), which we can use to extract specific components from dates, timestamps, and intervals.
In this article, we’ll look how the date_part() function works, along with some basic examples.
DuckDB provides us with an easy way to add one or more months to date values, as well as subtract from them. This applies to the various date related data types, such as DATE and TIMESTAMP.
Actually, DuckDB provides us with a choice; We can use the + operator or the date_add() function.
When working with DuckDB, sometimes we might need to get the weekday name from a date or timestamp value. And sometimes we might want just the abbreviated weekday name, rather than the full name. For example, we might want Mon instead of Monday.