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.
DBMS
Database Management Systems
6 Functions for Working with the Unix Epoch in DuckDB
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
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
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
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
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.
Output Query Results as a Tab-Separated List in DuckDB
DuckDB’s command-line interface (CLI) provides a simple built-in method for outputting query results as tab-separated values (TSV) using the .mode tabs command.
This article takes a quick look at this output mode and explores some of the options available for when outputting query results as a tab-separated list.
Parse and Minify JSON with DuckDB’s JSON() Function
One of the many JSON functions that DuckDB provides is the json() function, which parses and minifies JSON values. We pass the JSON data to the function, and it parses and minifies the JSON, then returns the result.
4 DuckDB Functions that Find the Position of a Value in a List
As with most other database management systems (DBMSs), DuckDB provides us with a way of finding the position of an element in a list. This is the index at which the specified element occurs in the list.
Actually, DuckDB provides us with four synonymous functions for doing this (i.e. they all do the same thing).
Get the Day Name from a Date in DuckDB
DuckDB provides us with a couple of ways to get the weekday name from a date or timestamp value. This could be the full day name, such as Monday, or the short/abbreviated day name, such as Mon.
Here are two ways to return the day name from a date in DuckDB.