Tag: dates

How to Use and Store Dates in SQLite

Posted on by Ian

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 reading →

How EPOCH_NS() Works in DuckDB

Posted on by Ian

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.

Continue reading →

5 Functions that Return the Year from a Date in DuckDB

Posted on by Ian

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.

Continue reading →

Examples of EPOCH_US() in DuckDB

Posted on by Ian

DuckDB provides us with a bunch of epoch...() functions that enable us to get the Unix epoch time from a given date/time value. Different functions return their result using different units (for example seconds, milliseconds, etc). The epoch_us() function returns its result in microseconds.

Unix epoch time is typically expressed as the number of seconds that have elapsed since January 1, 1970 (UTC), but epoch_us() function returns the equivalent amount in microseconds.

Continue reading →

A Quick Look at EPOCH_MS() in DuckDB

Posted on by Ian

In DuckDB, the epoch_ms() function serves a dual purpose. It converts timestamp values into Unix epoch time in milliseconds and also performs the reverse operation, transforming Unix epoch time values back into timestamps.

Unix epoch time is typically expressed as the number of seconds that have elapsed since January 1, 1970 (UTC), but this function returns the equivalent amount in milliseconds.

The function is similar to the epoch() function, which returns its result in seconds. However, the epoch() function only works in one direction; it converts a timestamp value to epoch time, but it doesn’t work the other way around like epoch_ms() can.

Continue reading →

An Overview of DuckDB’s EPOCH() Function

Posted on by Ian

DuckDB’s epoch() function is a useful time conversion tool that allows us to transform timestamp values into Unix epoch time – the number of seconds that have elapsed since January 1, 1970 (UTC).

The function is particularly useful when working with time-series data and when we need to perform mathematical operations on timestamp values. By converting timestamps to integer representations, we can easily calculate time differences, group time-based data, or integrate with systems that use epoch time.

Continue reading →

4 Ways to Get the ISO Year from a Date in DuckDB

Posted on by Ian

In the ISO-8601 calendar, years begin on the first Monday closest to January 1, meaning the start date can fall between December 29 and January 4. This differs from the Gregorian calendar, where years always start on January 1, leading to potential discrepancies around these dates. Additionally, ISO-8601 years, or just ISO years, can be either 52 or 53 weeks long, depending on their starting point.

This article provides four options for extracting the ISO year from a date in DuckDB, accounting for these unique calendar rules.

Continue reading →