An Overview of DuckDB’s EPOCH() Function

Posted on by Ian

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 →

2 Ways to Check if a Table has any Partial Indexes in SQLite

Posted on by Ian

In SQLite, a partial index includes only the rows that meet a specified condition, set by a WHERE clause in the index definition. This is different from a regular, or “full,” index, which covers all rows in a table. Partial indexes are helpful when you frequently query specific subsets of data, as they can improve query performance and reduce storage.

If you ever need to check a table to see if it contains any partial indexes, you can use either of the following methods.

Continue reading →

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

Posted on by Ian

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.

Continue reading →

Understanding LIST_ZIP() in DuckDB

Posted on by Ian

DuckDB has a list_zip() function that zips one or more lists to a new list with a length of the longest list provided. The new list contains unnamed structs of the elements from the original lists. We pass the lists to the function when we call it. We can also pass a Boolean value to specify whether or not to truncate all lists to the smallest list.

Below are some basic examples that demonstrate how the list_zip() function works.

Continue reading →