Working with dates often requires extracting specific components, such as the week number, for analysis or reporting. In DuckDB, there are multiple functions to retrieve the week from a date, each catering to different needs. This article explores four such functions, including how to calculate the ISO week—a standard defined by ISO-8601 where weeks start on Monday and the first week of the year contains the year’s first Thursday.
Continue readingTag: how to
Fix “Unrecognized format for strftime/strptime: %” in DuckDB
If you’re getting an error that includes the text “Unrecognized format for strftime/strptime: %” in DuckDB, it appears that you’re including an unescaped percent sign (%) in your format string when using a function like strftime() or strptime().
Whenever we need a percent sign to be included in the formatted output, we must escape it with another percent sign in the format string.
So to fix this issue, try escaping the percent sign with another percent sign.
Continue readingAdd Hours to a Date/Time Value in DuckDB
DuckDB provides us with a range of functions and operators that we can use to manipulate date, time, and timestamp values. One basic operation you might find yourself having to perform is date/time arithmetic, such as adding one or more hours to a date/time value.
Below are two methods we can use in order to add one or more hours to a date, timestamp, or time value.
Continue readingWhy SQLite Allows NULL Values in Primary Key Columns
SQLite, one of the most widely used database engines, is known for its lightweight design, ease of use, and adherence to most aspects of the SQL standard. However, one notable deviation from the standard lies in its handling of PRIMARY KEY constraints. Unlike the SQL standard, SQLite allows NULL values in primary key columns in some cases.
Let’s look at the reasons behind this behavior, and explore the implications of NULL values in primary key columns. We’ll also examine SQLite’s treatment of NULL values as distinct for uniqueness constraints.
Using the .nullvalue Command to See NULL Output in the DuckDB CLI
By default, when a query returns a null value in the DuckDB command line interface (CLI), an empty string is displayed. This may or may not be what you want. But if you’re like me, you probably want DuckDB to explicitly tell you that it’s a null value. After all, if an empty string is returned, perhaps the data contained an empty string?
Fortunately, DuckDB provides us with the .nullvalue dot command so that we can change the output of null values.
7 Functions that Return the Current Date in DuckDB
DuckDB provides us with a bunch of functions that we can use to return the current date and/or time. We can get the date in local time or coordinated universal time (UTC), depending on the function we use. These can be useful in a range of scenarios, such logging the current date in a database column, or filtering or comparing dates based on the current date.
Continue readingFix “Error: Month out of range, expected a value between 1 and 12” when using strptime() in DuckDB
If you’re getting an error that reads “Error: Month out of range, expected a value between 1 and 12” when using the strptime() function in DuckDB, it could be that you’re getting your months mixed up in your format string.
How to Check a Table for Unique Indexes in SQLite
Being able to ensure uniqueness in database tables is critical for maintaining data integrity. SQLite allows us to create unique indexes in order to enforce uniqueness, and it provides us with tools to check the unique indexes associated with a table.
This article outlines how to use the SQLite PRAGMA index_list() command to check a table for unique indexes.
7 Functions that Extract the Day from a Date in DuckDB
When working with dates in DuckDB, extracting specific components like the day is a common task. DuckDB provides a good range of functions that we can use to help us to perform such a task.
This article presents seven different functions we can use to extract the day from a date in DuckDB.
Continue readingHow to Format Dates in DuckDB
When working with SQL databases such as DuckDB, dates usually conform to a specific format. In particular, when we create dates in DuckDB, they must conform to the ISO 8601 format (YYYY-MM-DD). It’s the same with time (hh:mm:ss[.zzzzzz][+-TT[:tt]]) and timestamp values (YYYY-MM-DD hh:mm:ss[.zzzzzzzzz][+-TT[:tt]]).
But what if we have a requirement to present these dates or timestamps in a different format?
Fortunately, DuckDB provides us with tools to so. This article explains how to format date and timestamp values according to a specified format.
Continue reading