Database.Guide – Page 28

2 Ways to Get the Month Name from a Date in DuckDB

July 19, 2025 by Ian

DuckDB offers a pretty good range of functions that enable us to get date parts from date or timestamp value. For example, we can extract the month part from a given date value. In most cases, this will be the month number, for example 08 or just 8.

But sometimes we might want to get the actual month name, like October for example. And other times we might just want the abbreviated month name, like Oct.

Fortunately, DuckDB’s got our back. Here are two ways to return the month name from a date or timestamp value in DuckDB.

Appending Values to Lists with 3 DuckDB Functions

July 18, 2025 by Ian

Appending values to lists is a common task when working with DuckDB and other DBMSs (not to mention programming languages in general). Usually we’ll get a function that’s purpose built for the task. As it turns out, DuckDB provides us with at least three functions to do this task.

3 Functions for Extracting a Value from a List in DuckDB

July 17, 2025 by Ian

DuckDB provides us with a list_extract() function that extracts a value from a list, based on the element’s index in that list.

But as if one function isn’t enough, it also provides us with a couple of aliases that we can use instead of list_extract() if we prefer.

So let’s call it three functions that we can use to extract a value from a list.

DUCKDB_TABLES() Examples

November 15, 2025July 16, 2025 by Ian

The duckdb_tables() function is a system function in DuckDB that provides useful metadata about all tables in your database. The function returns a table containing information about each table, including its schema, name, and various properties. It can be quite a handy tool for database introspection and management.

Generating Date Ranges with DuckDB Queries

July 15, 2025 by Ian

Working with date ranges is a common requirement in data analysis, reporting, and time-series operations. DuckDB provides us with several approaches for generating date ranges. This article explores various techniques for creating date ranges in DuckDB.

6 Functions for Working with the Unix Epoch in DuckDB

July 14, 2025 by Ian

DuckDB offers a versatile set of functions to handle timestamps at various levels of precision. This article explores some of DuckDB’s functions that help us to convert between epoch representations and timestamps.

These specialized time conversion functions can be handy tools when working with temporal data, allowing seamless translation between human-readable timestamps and machine-optimized epoch representations at varying levels of precision.

The Difference Between DATE_DIFF() and DATE_SUB() in DuckDB

July 13, 2025 by Ian

In DuckDB, the date_diff() (along with its synonym datediff()) and date_sub() (along with its synonym datesub()) functions allow us to get the difference between two dates. While they might seem similar, they actually calculate date differences in distinct ways that are important to understand for accurate data analysis.

Let’s take a look at the difference between these functions.

2 Functions that Create a List in DuckDB

July 12, 2025 by Ian

DuckDB provides us with plenty of functions for working with lists, including a couple that actually create lists for us.

In particular, the list_value() and list_pack() functions are specifically for creating lists in DuckDB. We’ll run through some examples of these functions below.

Using JSON_TRANSFORM_STRICT() to Perform Strict JSON Transformations in DuckDB

July 11, 2025 by Ian

DuckDB provides us with a json_transform_strict() function that works just like the json_transform() function, except that it throws an error when type casting fails. The json_transform() function on the other hand, returns NULL for the respective field in such cases.

5 Ways to Convert a List to a String in DuckDB

July 10, 2025 by Ian

If we have a list in DuckDB, and we want to convert it to a string, there are a number of options on the table. Basically, what we want to do is combine/concatenate all elements into a single string. Let’s take a look at five ways to convert a list to a string in DuckDB.