dates

4 Ways to Get the ISO Year from a Date in DuckDB

by

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.

Read more

Subtracting Minutes from a Date/Time Value in DuckDB

by

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.

Read more

Working with Intervals in DuckDB

by

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.

Read more

Add Months to a Date in DuckDB

by

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.

Read more

Understanding EXTRACT() in DuckDB

by

DuckDB offers a variety of date functions, one of which is the extract() function. This function is designed to retrieve a specific date part from a date or timestamp value, and it can also be applied to intervals.

In this article, we’ll take a closer look at the extract() function and provide some straightforward examples to illustrate how it works.

Read more

Add Seconds to a Date/Time Value in DuckDB

by

Performing date/time arithmetic is a common operation when using SQL databases such as DuckDB. As with most RDBMSs, DuckDB makes these types of operations relatively straightforward for us.

Here are two options for adding one or more seconds to a date, timestamp, or time value.

Read more

Using TRY_STRPTIME() to Handle Errors When Constructing Timestamps in DuckDB

by

If you’ve ever used the strptime() function to create a timestamp in DuckDB, you may be aware that it will return an error if it can’t construct the timestamp from the format string/s provided.

While such an error could be useful in some situations, it could also be annoying in others.

Fortunately, DuckDB also provides the try_strptime() function, which will suppress any error that we might ordinarily get in such cases. This function returns null instead of an error.

Read more