DuckDB provides us with a good selection of functions for working with dates and timestamps. One of the things we might find ourselves needing to do is extracting the ISO weekday from a date or timestamp—a numeric value where Monday is represented as 1 and Sunday as 7.
This article presents four functions we can use to get the ISO weekday from a date in DuckDB.
In DuckDB, the strptime() function converts a date/time string into a valid timestamp value. We pass a format string to the function in order to tell it what format our string uses. This can be handy if we ever need to construct timestamps based on date/time strings that may or may not be in a valid format.
But what if we have multiple date/time strings in different formats?
Fortunately, the strptime() function caters for this scenario too.
DuckDB has a today() function that returns the current date. It’s similar to the current_date function, which does the same thing.
Here, we’ll look at how the today() function works, along with some basic examples.
When working with SQL databases, one operation we often find ourselves performing is date/time arithmetic, such as adding or subtracting an interval to/from a date/time value. Fortunately, most RDBMSs make such operations quite easy to achieve, and DuckDB is no exception.
Below are two methods we can use in order to subtract hours from a date, timestamp, or time value in DuckDB.
When working with dates in DuckDB, sometimes we need to extract date parts from date or timestamp values. And when it comes to date parts like days and months, we have the option of getting the numeric representation or the actual name. And if we want the name, we have a further option of getting the full name or the shortened version.
For example, we can get December or we can get Dec.
Like most other DBMSs, DuckDB provides allows us to add and subtract intervals to/from date, timestamp, and time values. To perform a subtraction, we can use the minus (-) operator or the date_add() function (in conjunction with the minus operator).
Below are examples of using each of these options to subtract seconds from date/time values.
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.
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.
DuckDB provides an epoch_ns() function that returns the Unix epoch time from a given date/time value in nanoseconds.
Unix epoch time is typically expressed as the number of seconds that have elapsed since January 1, 1970 (UTC), but it can also be expressed in other units, such as milliseconds, microseconds, and nanoseconds. DuckDB has specific functions for each of these units, with epoch_ns() being the function that returns it in nanoseconds.
When working with dates in DuckDB, some common tasks we might need to perform include extracting date parts from a date or timestamp value. For example we might want to extract the year from a date. Fortunately, DuckDB provides us with an abundance of options for doing that.
In this article, we’ll look at five different functions extract the year from a date in DuckDB.