Category: Relational

A Quick Look at EPOCH_MS() in DuckDB

Posted on by Ian

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.

Continue reading →

Using DuckDB’s FSUM() Function for More Accurate Results

Posted on by Ian

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.

Continue reading →

A Quick Look at DuckDB’s WEIGHTED_AVG() Function

Posted on by Ian

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.

Continue reading →

Using TRY_CAST() to Handle Errors When Converting Between Data Types in DuckDB

Posted on by Ian

Encountering errors while converting between data types can be frustrating when working with SQL databases like DuckDB. But it usually means that something’s wrong. In most cases these errors occur because we’re trying to perform an impossible conversion, like from a number to a date or something.

But sometimes errors can get in the way, especially when we’re trying to convert a bunch of values. Sometimes it would be better for the system to return NULL for such failed conversions than to return an error and mess up the whole operation. Fortunately, we can do this.

Continue reading →

A Quick Look at the LIST_AGGREGATE() Function in DuckDB

Posted on by Ian

DuckDB’s list_aggregate() function is a handy tool for performing grouped aggregations over lists. It allows us to apply any aggregate function (like sum, avg, min, max, count, etc.) to a list column as if each list item were a row.

The way it works is that we pass the list as the first argument, followed by the name of an aggregate function we want to apply to that list. The function will return its result as if it were the named aggregate function.

Continue reading →