DuckDB provides us with a few options for slicing lists. Slicing a list is where we take a sublist or “slice” from the list, rather than the whole list. We specify the start and end position for which to return the slice. We can also specify a step, which allows us to skip one or more elements along the slice.
Continue readingTag: how to
Using Shorthand to Perform Data Conversions in DuckDB
When it comes to converting between data types, DuckDB performs implicit conversions when required, while also enabling us to perform explicit conversions. Implicit conversions are performed automatically by DuckDB when we do some other operation, such as use a function that requires its argument/s in a different data type than the one we’re providing. Regarding explicit conversions, we have the option of using a function like cast() or try_cast(), or using the shorthand method.
In this article we’ll take a quick look at how to convert between data types using the shorthand method.
Continue readingGet the Number of Elements in a JSON Array with JSON_ARRAY_LENGTH() in DuckDB
In DuckDB, the json_array_length() function is used to determine the number of elements in a JSON array. This function can return the count of items in the top-level array or in a nested array within it. It’s especially useful when working with complex JSON structures where arrays are embedded inside objects or other arrays. By combining it with JSON path expressions, you can target specific portions of the data for more precise analysis.
4 Options for Extracting the First Value from a List in DuckDB
When working with lists in DuckDB, sometimes you might want to return just the first value, rather than the whole list. Fortunately, DuckDB provides us with several ways to do this.
Continue readingCreate a TIMESTAMP Value in DuckDB with MAKE_TIMESTAMP()
The make_timestamp() function in DuckDB is a handy tool for creating timestamp values from individual components. It allows us to construct timestamp values using two alternative approaches; by specifying the number of milliseconds from epoch, or by specifying the year, month, day, hour, minute, and second components separately.
Let’s take a look at the make_timestamp() function, along with some examples.
How to Use and Store Dates in SQLite
SQLite is a lightweight, self-contained relational database management system that is widely used due to its simplicity and portability. However, one notable aspect of SQLite is its lack of a dedicated DATE or DATETIME storage class. Instead, SQLite stores date and time values as integers, real numbers, or text, depending on how the developer chooses to manage these values.
In this article, we’ll explore how to effectively use and store dates in SQLite, and provide examples that may help you implement date management in your projects.
Continue reading3 Easy Ways to Calculate the Median Value from a List in DuckDB
While we might typically use DuckDB’s median() function to get the median value from a column, when it comes to getting the median value from a list, we need to modify this slightly. A list can contain many values, and so we need a way to calculate the median of all of those values, rather than a single value like we’d normally be doing when calculating the median value from a column.
Fortunately, DuckDB provides us with several easy options for achieving this. Below are three options for getting the median value from a list in DuckDB.
Continue readingRPAD() Alternative: Applying Right Padding in SQL Server
The SQL rpad() function has been widely implemented across many major RDBMSs, including MySQL, Oracle, PostgreSQL, and MariaDB, to name just a few. But when it comes to SQL Server, we have a problem. SQL Server doesn’t currently provide us with an rpad() function.
But that’s not to say we can’t apply right padding in SQL Server. SQL Server still provides us with enough tools to get the job done. With a bit of work, we can get a similar result to what we might be able to achieve with rpad(). It may not be as elegant as a simple rpad() function, but at least it’s an option.
3 DuckDB Functions that Prepend a Value to a List
DuckDB provides us with a few functions that prepend a value to a list. Actually, these functions are synonymous, so they all do the same thing, with the same/similar basic syntax.
Continue readingUsing LIST_INTERSECT() to Extract Overlapping Elements from Two Lists in DuckDB
The list_intersect() can be quite a handy function when working with lists in DuckDB. It accepts two lists as arguments, and it returns a list of elements that are common to both lists. It essentially performs a set intersection operation while preserving the list data type.
Duplicate values are ignored, so only one value is returned even if there are multiple occurrences in one or both lists.
Continue reading