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.
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.
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.
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.
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).
DuckDB offers two primary functions for type conversion: cast() and try_cast(). While they serve similar purposes, their behavior when handling invalid conversions differs significantly, which can greatly impact our data processing workflows.
DuckDB has a json_keys() function that we can use to extract the keys from a JSON object. It returns the keys as a list of strings (LIST of VARCHAR).
You may be aware that DuckDB includes a typeof() function that works just like the SQLite equivalent; it returns the data type of its argument. But did you know that DuckDB also provides us with a pg_typeof() function that does essentially the same thing?
So why would DuckDB need a pg_typeof() function that does basically the same thing as typeof()? Let’s find out!
DuckDB has a list_where() function that returns a list after a user-supplied mask has been applied. We pass the list as the first argument, and a list of Boolean values as the second. The list of Boolean values is applied as a mask to the list, which determines which values in the list are returned.
DuckDB provides us with a good selection of functions for working with date and timestamp values. One such function is the LAST_DAY() function, which returns the last day of the month of the given date.
The way it works is, we pass a date to the function, and it returns the date of the last day of that month.