Press "Enter" to skip to content

Category: Dates and Numbers

Aggregating by Month and Year in R

Published 2024-01-09 by Kevin Feasel

Steven Sanderson groups by month and year:

Taming the beast of daily data can be daunting. While it captures every detail, sometimes you need a bird’s-eye view. Enter aggregation, your secret weapon for transforming daily data into monthly and yearly insights. In this post, we’ll dive into the world of R, where you’ll wield powerful tools like dplyr and lubridate to master this data wrangling art.

Click through for examples of summarizing daily data into monthly and annual data. One thing to keep in mind, however, is that the monthly aggregation in these examples is just month, so if you have July 2023 and July 2024 data, you’ll get a row back for July. It’s all about understanding what the grain of your data is, as well as your desired grain.

Comments closed

Time Series Data in Postgres with TimescaleDB

Published 2023-11-10 by Kevin Feasel

Semab Tariq keeps track of time:

TimescaleDB is an open-source time-series database extension for PostgreSQL. It is designed to efficiently manage and query time-series data, offering features such as automatic data partitioning, data retention policies, and specialized time-series functions. 

This extension provides scalability, improved performance, and seamless integration with PostgreSQL, making it a powerful choice for applications dealing with large volumes of time-stamped data, including IoT, monitoring, and analytics.

Read on to learn how to install it (on Linux), some of the tuning parameters available, and how to create time series hypertables and chunk tables.

Comments closed

CAST() and CONVERT() for Dates

Published 2023-10-25 by Kevin Feasel

Chad Callihan converts a date:

CAST and CONVERT can both be used to switch a value to a new data type. They are similar, but certainly not identical. While CAST is considered ANSI SQL and will get you across the finish line, CONVERT can give you more flexibility when it comes to formatting date values. Let’s look at an example comparing the usage of CAST and CONVERT with dates.

Most of the time, I’ll use CAST() over CONVERT(), not so much because the former is ANSI compliant, but rather because I think it’s more intuitive to remember. Date formatting is one of the few occasions in which I usually prefer CONVERT() and that’s precisely because of the format options. Of course, if you want more custom formatting options, you can use FORMAT(), though that function uses .NET in the background and is remarkably slow. It’s fine if you’re formatting a few dates, but if you’re outputting millions of rows, you will certainly see a marked difference.

Comments closed

PostgreSQL 16 and Infinity

Published 2023-09-01 by Kevin Feasel

Ryan Lambert goes to infinity and beyond:

This month, Ryan Booz chose the topic: What Excites You About PostgreSQL 16? With the release of Postgres 16 expected in the near(ish) future, it’s starting to get real. It won’t be long until casual users are upgrading their Postgres instances. To decide what to write about I headed to the Postgres 16 release notes to scan through the documents. Through all of the items, I picked this item attributed to Vik Fearing.

  • Accept the spelling “+infinity” in datetime input

The rest of this post looks at what this means, and why I think this matters.

Read on to see what’s new about this and what it all means.

Comments closed

ADX Date and Time Representations in Power Query and Power BI

Published 2023-08-28 by Kevin Feasel

Dany Hoter does some explaining:

Data in ADX (aka Kusto aka RTA in Fabric) almost always has columns that contain datetime values like 2023-08-01 16:45 and sometimes timespan values like 2 hours or 36 minutes.

In this article I’ll describe how these values are represented in ADX in Power Query and in Power BI.

Notice that I don’t just say Power BI because timespan values have different types in Power Query and in Power BI.

Read on for those details.

Comments closed

DATEDIFF() and Month Boundaries

Published 2023-08-22 by Kevin Feasel

Deb Melkin fed the mogwai after midnight:

I was working on a query this week that reminded me of a fun quirk when working with dates and the DATEDIFF function in particular.

I have a process that takes a while to run. Because of all of the moving parts to keep track of, I have an audit table to track what I’m doing to collect basic info like when did it start, when did it end, etc. I created a simple report for myself to break things down so I can report back to the team. I threw together a simple SQL statement, using DATEDIFF to figure out the how long things took. Looking at the results, I saw some interesting results.

Read on for two queries, one which has a bit of a problem and one which strives to correct that problem.

Comments closed

Creating a Time Dimension in Power BI via DAX

Published 2023-08-14 by Kevin Feasel

Angela Henry gets a watch:

There are some instances when you want to analyze data over time, not just dates. Most of us are familiar with having to create date tables and use them in analysis, but having to analyze data over time is not as common. Let’s say you run a taxi company and you want to determine when your busiest times of day are. This would come in handy for scheduling drivers. You need more drivers during busy times because no one wants to wait for a taxi!

Read on to see one way to create the table in Power BI.

Comments closed

Creating a Simple Date Dimension in Databricks

Published 2023-08-10 by Kevin Feasel

Chen Hirsh builds a table:

A date dimension is extremely useful and is required by most BI applications. This kind of dimension has a key of time level (day, month, etc.), and attributes that describe it such as year, month, etc. In your BI model, you join this dimension to facts on their date fields, to aggregate from day level to week, month, and year.

In this post, I will demonstrate how to create a date dimension on Azure Databricks using Python. A link to the complete Databricks notebook is at the end of the post.

Check out the code, as well as explanation, in that post.

Comments closed