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.
Continue readingDuckDB offers a variety of utility functions to help us better understand the data that we need to work with. Among these functions, typeof() serves as a useful tool for type inspection and validation. In this article, we’ll explore how this function works, along with examples that demonstrate its usage.
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 readingDuckDB’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 readingSometimes when working with DuckDB, we need to find the maximum value from a given list. This can often be the case when working with lists that contain numeric data. Fortunately DuckDB provides a few options for us for doing this.
Continue readingMost SQL databases provide a cast() function and DuckDB is no exception. The purpose of the cast() function is to convert a value from one data type to another. This can also be referred to as casting the value as the other data type.
Whenever we use DuckDB’s generate_series() function, the results are returned in a list. But sometimes we might want the results to be returned in a table instead of a list, so that each value in the series is presented as a separate row in a table (as opposed to being an item in a list). In such cases we would need to “unnest” the results.
DuckDB has a list_slice() function that enables us to select multiple list items from a list, based on their index range within the list. For example, we can select all list items between position 5 and 10 in a list.
If you’re getting a binder error that reads something like “No function matches the given name and argument types ‘list_concat(STRING_LITERAL, VARCHAR[][])’. You might need to add explicit type casts.” in DuckDB when using either the array_push_front() or array_push_back() functions, it could be due to a slight syntax error.
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.
Continue reading