When working with dates in DuckDB, some common tasks we might need to perform include extracting date parts from a date or timestamp value. For example we might want to extract the year from a date. Fortunately, DuckDB provides us with an abundance of options for doing that.
In this article, we’ll look at five different functions extract the year from a date in DuckDB.
Continue readingDuckDB 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.
When working with large datasets in DuckDB, the SAMPLE clause offers an efficient way to query a subset of your data. However, unless you specifically construct your query to get repeatable results, this sampling will return a different set of results each time the query is run.
But we can change that. We can write our query to return the same random result set every time we run it.
This article explores how to achieve consistent, reproducible result sets when using the SAMPLE clause in DuckDB.
When working with lists in DuckDB, it’s often useful to know how many elements you’re working with. Fortunately DuckDB provides us with several functions that help us determine this. Below are three options for counting up the elements in a list when using DuckDB.
Continue readingMost DuckDB distributions are shipped with the json extension, which enables us to work with JSON documents. DuckDB provides a bunch of JSON functions, including JSON_EXISTS(). The JSON_EXISTS() function allows us to check whether a specific path exists within a JSON document.
This article explores how this function works, along with examples.
Continue readingIn 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.
DuckDB has a fsum() function that can be used instead of the regular sum() function in order to get more accurate results. fsum() calculates the sum using a floating point summation method known as Kahan summation (or compensated summation).
This method helps reduce the accumulation of rounding errors that can occur when summing many floating point numbers when using the regular sum() function.
In analytical SQL workloads, expressing weighted averages can sometimes involve verbose expressions such as combining the sum() function with other operators. DuckDB streamlines this with its native weighted_avg() aggregate function, allowing us to compute weighted averages directly and efficiently. The weighted_avg() function enhances both clarity and speed when dealing with data where values contribute unequally — such as population-adjusted metrics or revenue-weighted scores.
This article explores the weighted_avg() in DuckDB, along with examples to demonstrate its usage.
If you’re getting an error in DuckDB that includes “list dimensions must be equal“, it appears that you’re performing a list operation that requires the lists to be the same dimension, but the lists are of different dimensions.
To fix this error, be sure to use lists of equal dimensions when performing the operation.
Continue readingDate/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.
Continue reading