Press "Enter" to skip to content

Category: Dates and Numbers

Gaps and Islands in Dates

Published 2021-04-29 by Kevin Feasel

Aaron Bertrand shows off a great use for calendar tables in gap and island style queries:

In my previous article I revisited the concept of a calendar table, and explained some ways to use this data for business date calculations. This time, I wanted to explore how you can use the calendar table to simplify generating date ranges, and some query challenges this can help you simplify.

Click through for examples of the sorts of gap and island problems you can solve fairly easily with a calendar table. For an even simpler example, many BI reports want to see days even where there is no data, and a calendar table gives you that capability.

Comments closed

Arithmetic Operations on DATETIME Data Types

Published 2021-04-29 by Kevin Feasel

Eitan Bluman shows off some math skills:

Mathematical addition and subtraction can be performed between two datetime data types:

SET @d2 = '1900-03-30 18:00'SELECT@d1 + @d2 -- result: 1900-04-01 10:15:15.900, @d1 - @d2 -- result: 1899-10-05 22:15:15.900, @d2 - @d1 -- result: 1900-03-29 01:44:44.100

This means that we can have basic datetime arithmetics in SQL server. We can use subtraction to find an accurate difference between two dates, and use addition to add an accurate interval to a datetime column or variable.

This is one of those things you can do, but I’m not very fond of. First of all, as Eitan points out, you can’t do these in the (in all ways superior) DATETIME2 data type. Secondly, it adds some confusion to the code, as you don’t always get what you expect.

Comments closed

Using Calendar Tables

Published 2021-04-26 by Kevin Feasel

Aaron Bertrand has a post up on using a calendar table:

A while back, I wrote an article called Creating a date dimension or calendar table in SQL Server. I have used this pattern repeatedly and, based on the questions I get from the community, many of you are using it, too. Some of the questions I get are along the lines of “how do I actually use this table in my queries?” and “what are the performance characteristics compared to other approaches?” So, I thought I would put together a collection of use cases and analysis, starting with business day problems.

I’m a big fan of calendar tables as well. They’re quite useful for a variety of business problems and make date math problems really easy, especially when dealing with non-standard calendars (e.g., work weeks, fiscal years, figuring out what day Easter is).

Comments closed

Time Series Data in PostgreSQL

Published 2021-04-07 by Kevin Feasel

Michael Grogan has a few examples of working with time series data in PostgreSQL:

Tools such as Python or R are most often used to conduct deep time series analysis.

However, knowledge of how to work with time series data using SQL is essential, particularly when working with very large datasets or data that is constantly being updated.

Here are some useful commands that can be invoked in SQL to better work with time series data within the data table itself.

Click through for examples like using a window function to calculate moving averages and using time zones. H/T Mark Hutchinson.

Comments closed

Power Query and the First Day of the Week

Published 2021-04-02 by Kevin Feasel

Ed Hansberry decrees that henceforth, Thursday shall be the first day of the week:

By default, Power BI starts its week on Sunday, but that doesn’t work for every situation. Some companies prefer their week start on Monday, and I recently helped someone that needed their week to start on Thursday. Fortunately, by tweaking your date table in Power Query, you can make this change with no calculations at all.

Click through to see how.

Comments closed

Two Disliked Data Types

Published 2021-03-11 by Kevin Feasel

Aaron Bertrand has two bones to pick:

I am not often one to do the bare minimum, unless it comes to looking for things around the house. After about 22 seconds I throw my hands in the air and exclaim, “I can’t find it!” As my wife so kindly added: It usually turns out to be in a spot I already looked.

But when it revolves around SQL Server and opinions, I’m all over it. So I’m not going to talk about a data type today; I’m going to talk about two of them. One of them Brent already mentioned in his invite:

I kinda-sorta disagree with Aaron’s second choice. By that I mean that I fully agree with his premise: use UTC everywhere. But if you don’t use UTC everywhere, then use DATETIMEOFFSET everywhere and apply the time zones.

Comments closed

The Unique Properties of DateTimeOffset

Published 2021-03-09 by Kevin Feasel

Rob Farley analyzes a special data type:

And as I have a unique index on this, it won’t let me insert 00:30 in UTC+11, because 00:00 in UTC+10:30 is already there. It tells me “Msg 2627, Level 14, State 1, Line 19. Violation of PRIMARY KEY constraint ‘pkTimesOffset’. Cannot insert duplicate key in object ‘dbo.TimesOffsets’. The duplicate key value is (2021-01-01 00:30:00.0000000 +11:00).”

My general rule is to store everything in SQL Server as UTC. If I did not do this, I would very strongly advocate for using DateTimeOffsets regardless of the extra data length. I’ve experienced the pain of mismatched date and time details one too many times for that.

Fun bonus fact: the same applies to .NET as well. If I control the system, I’m using DateTime.UtcNow for everything. If not, I’m leaning heavily toward DateTimeOffset by default. Again, too many times have I experienced that source system X has times marked in Pacific Standard Time pushing data to a server in Eastern Standard Time, and then mixing in a server based in Central Standard Time and having people confused because “the times are wrong.”

Comments closed

Building a Function to Get the Next Date by Date Name or Offset

Published 2021-02-18 by Kevin Feasel

Louis Davidson has a function for us:

As I have been building my Twitter management software, I have been doing a lot more ad-hoc, repetitive coding using T-SQL directly. When I was generating new tweets for upcoming days, a bit of the process that got old quick was getting the date for an upcoming day (the primary key for my tweet table is the date, the type of the tweet, and a sequence number). After having to pick the date of next Tuesday… I had to write some more code (because a true programmer doesn’t do repetitive work when code can be written… even if sometimes the code doesn’t save you time for days or weeks.

So this following function was born from that need, and it is something I could imagine most anyone using semi-regularly, especially when testing software. 

This is definitely fancy. My inclination would be to create a calendar table, as that’ll solve this particular issue as well as other complex variants (like, I want the next Tuesday which doesn’t fall on a holiday).

Comments closed

Working with Calendar Tables

Published 2021-01-18 by Kevin Feasel

Peter Schott hits on one of my favorite concepts:

Maybe you’ve worked with data warehouses before, in which case the concept of a “Date Dimension” is going to be familiar. If not, the general idea behind a Calendar or Date table is that you have a table of Dates and metadata about those dates. This can include business-specific flags, alternate Quarter structures, alternate Week Start data, or whatever fits your needs

By pre-populating all of the data about a date in a table, it makes querying for specific date-based criteria a lot easier, especially when your fiscal year isn’t aligned with the calendar year or you need to deal with multiple fiscal years. It also helps with those holidays which are aligned with lunar calendars and thus “change date” every year.

Comments closed

Power BI and Unix Epoch Time

Published 2021-01-18 by Kevin Feasel

Ed Hansberry knows what time it is:

You may need to write a Power BI report that works with Unix Epoch Time, converting either to or from it. Epoch Time is simply the number of seconds that have passed since January 1, 1970, at 12:00 am UTC. So at 1 am of that date, Epoch Time would be 3600, since 3,600 seconds have transpired. Today Epoch Time is around 1610644465, or 1,610,644,465 to make it easier to read, though you will never see the commas in a database.

Converting to and from this time format is deceptively simple, as long as you understand a few functions in Power Query and the concept of Duration.

Read on for a demo.

Comments closed