DuckDB has a today() function that returns the current date. It’s similar to the current_date function, which does the same thing.
Here, we’ll look at how the today() function works, along with some basic examples.
DuckDB
Subtract Hours from a Date/Time Value in DuckDB
When working with SQL databases, one operation we often find ourselves performing is date/time arithmetic, such as adding or subtracting an interval to/from a date/time value. Fortunately, most RDBMSs make such operations quite easy to achieve, and DuckDB is no exception.
Below are two methods we can use in order to subtract hours from a date, timestamp, or time value in DuckDB.
Get the Abbreviated Month Name in DuckDB
When working with dates in DuckDB, sometimes we need to extract date parts from date or timestamp values. And when it comes to date parts like days and months, we have the option of getting the numeric representation or the actual name. And if we want the name, we have a further option of getting the full name or the shortened version.
For example, we can get December or we can get Dec.
3 Ways to Get the Weighted Average in DuckDB
Weighted averages are common calculations in data analysis, allowing us to assign different levels of importance to individual values in our dataset. Unlike simple averages, where each value has equal impact, weighted averages let us incorporate the relative significance of each observation. This is particularly valuable for scenarios like calculating GPA (where courses have different credit weights), investment portfolio returns (where assets have varying allocations), or quality ratings (where reviewers have different expertise levels).
In this article, we’ll explore three ways of calculating weighted averages in DuckDB.
A Quick Look at LIMIT & OFFSET in DuckDB
Most database management systems (DBMSs) provide us with a means of restricting the number of rows returned by a query to a fixed number of rows, or to a percentage of the data set. In many cases this is done with a LIMIT clause (although some DBMSs provide other methods, such as SQL Server’s TOP clause).
When it comes to DuckDB, the LIMIT clause is what’s implemented for this functionality.
Output Query Results to a JSON File in DuckDB
DuckDB makes it easy to export query results to JSON format, which can help us integrate database outputs with web applications, APIs, and data processing pipelines. This article explores how to generate JSON files from DuckDB queries.
Extract All Values From a JSON Document With DuckDB’s JSON_TRANSFORM() Function
The json_transform() function in DuckDB is a handy tool for converting JSON strings into structured data types like STRUCT, MAP, and LIST. This allows you to directly query and manipulate nested JSON data using standard SQL, making it much easier to work with complex JSON objects and arrays.
Think of it as a way to cast your JSON data into a more usable, typed format within your database.
Performance Tip for Extracting Multiple Values from JSON in DuckDB
DuckDB has a bunch of functions that allow us to extract data from JSON documents. For example, there’s the json_extract() function, which extracts JSON from the specified JSON document.
Often times we’ll need to extract multiple values within the same query. For example, we may need to extract both a user’s name and age, so that they’re returned in two separate columns.
Using JSON_TYPE() in DuckDB to Get the Data Type of a Given JSON Value
DuckDB’s json_type() function is a useful utility for inspecting JSON data structures. It helps us determine what type of data we’re working with at any level within a JSON document. This function can be handy when we need to validate data types or handle different JSON structures programmatically.
2 Ways to Subtract Seconds from a Date/Time Value in DuckDB
Like most other DBMSs, DuckDB provides allows us to add and subtract intervals to/from date, timestamp, and time values. To perform a subtraction, we can use the minus (-) operator or the date_add() function (in conjunction with the minus operator).
Below are examples of using each of these options to subtract seconds from date/time values.