dates

Using the AGE() Function to Compare Dates in DuckDB

by

When working with date and time data in DuckDB, calculating the difference between two dates is a common requirement. Whether we’re determining a person’s age from their birthdate or measuring the duration between two events, DuckDB’s age() function provides a straightforward solution. This function returns an interval representing the difference between two timestamps or dates, making it especially useful for time-based analyses.

In this article, we’ll explore how to use the age() function in DuckDB. We’ll cover its syntax, and provide some simple examples.

Read more

Fix ‘Conversion Error: extract specifier “monthname” not recognized’ in DuckDB

by

If you’re getting an error that reads “Conversion Error: extract specifier “monthname” not recognized” in DuckDB, it appears that you’re using a function like extract() or date_part() to try to get the month name from a date.

These functions don’t accept a monthname specifier, and so that’s why the error occurs. Fortunately, DuckDB provides a monthname() function, and so you could try that instead. Also, the strftime() function has a format specifier for the month name, and so that’s another option.

So to fix this issue, try the monthname() or strftime() function instead.

Read more

Checking if a Value is Finite in DuckDB with ISFINITE()

by

In DuckDB, ISFINITE() is a function for checking whether values are finite. DuckDB supports infinite values, and so we can use this function to check whether a value is infinite or not. This can be useful when working with floating-point data that might contain special values like NaN (Not a Number) or infinity. We can also use it on date and timestamp values.

Read more

Getting a List of Time Zones in DuckDB

by

Sometimes we need to specify a time zone when constructing timestamp values in DuckDB. But we may not always know the exact value to use for the time zone.

Fortunately, we can use the pg_timezone_names() table function to get a list of available timezones in DuckDB.

Read more

4 Functions that Get the Week From a Date in DuckDB

by

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.

Read more

Fix “Unrecognized format for strftime/strptime: %” in DuckDB

by

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.

Read more