dates

How to Format Dates in DuckDB

by

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.

Read more

List of DuckDB Format Specifiers for Date Formats

by

In DuckDB, we can use functions like strftime() and strptime() to format date or timestamp values. These functions accept a format string that specifies how the date or timestamp should be formatted.

The format string consists of one or more format specifiers. For example, '%d/%m/%Y' is a format string that consists of three format specifiers. The output from that format specifier might look something like 05/09/2050.

Read more

2 Ways to Add Days to a Date in DuckDB

by

One common operation you may find yourself doing occasionally in DuckDB is adding intervals to date and/or time values. DuckDB provides us with a couple of easy ways to do this.

Here are two ways to add days to a date in DuckDB.

Read more

A Quick Look at DuckDB’s CURRENT_DATE Function

by

DuckDB is an in-process SQL OLAP database management system designed for analytical workloads. It is known for its speed, efficiency, and ease of use. One of the many functions DuckDB provides is current_date, which is useful for working with date-related data.

In this article, we’ll look at how the current_date function works, along with some straightforward examples.

Read more

Understanding Julian Day

by

Julian day is a concept you might occasionally encounter in SQL code or database operations, particularly when working with date and time functions. While it may seem esoteric at first, understanding Julian day can be incredibly useful for handling date calculations, especially in fields like astronomy, data analysis, and historical research.

This article looks at the origins, calculations, and practical applications of Julian day, including examples of converting between Julian day and other date formats in SQL.

Read more

How to Set the datestyle Variable for the Current Session in PostgreSQL

by

PostgreSQL has a datestyle variable that specifies the display format for date and time values, as well as the rules for interpreting ambiguous date input values. 

We can set the date/time style with the SET datestyle command, the DateStyle parameter in the postgresql.conf configuration file, or the PGDATESTYLE environment variable on the server or client.

Below is an example of using the SET datestyle command to change the datestyle for the current session.

Read more