Press "Enter" to skip to content

Category: Dates and Numbers

Sargability and Dates

Chad Callihan makes me want to change the title to “Getting Sarge a Date”:

We’ll use the StackOverflow2013 database for this example. Let’s say we want to return the users created in 2013. One way to return this data is to use the YEAR() function to pull out the desired year for our query:

For the reference, check out Chad’s prior post. My expectation is that about 90% of people in the US who are aware of the term pronounce it “Sarge-able” instead of “Sar-guhble” and therefore immediately think of Sergeants.

1 Comment

Wildcards and Data Type Precedence

Daniel Hutmacher has fun with implicit conversion:

Implicit conversions in SQL Server follow a specific, predictable order, called data type precedence. This means that if you compare or add/concatenate two values, a and b, with different data types, you can predict which one will be implicitly converted to the data type of the other one in order to be able to complete the operation.

I stumbled on an interesting exception to this rule the other day.

Click through for an example of date searching with LIKE.

Comments closed

Time Series Insights in Azure

Aveek Das explains the notion of Azure Time Series Insights:

In this article, we are going to learn in detail about Azure Time Series Insights. Microsoft Azure is one of the leading cloud providers these days. With a lot of companies adopting or migrating to the cloud these days, it has become a usual trend to convert existing technologies into cloud-based services and consume them. This not only helps the companies to reduce their cost but also in turn allows them to focus on more business-related problems rather than concentrating on infrastructure costs.

Azure Time Series Insights is one of the cloud services that users can use to integrate with their data that is constantly changing with time such as data from various sensors or machines, data from satellites, airlines etc. Any data that can be generated on a high scale and needs to be analysed, can be used through Azure Time Series Insights. In this article, we will focus on a high-level introduction of this service along with some use cases in detail.

Read on for the article.

Comments closed

Combining Windows and SQL Server Time Zone Info

Andy Mallon splices together time zone information like some kind of time zone Dr. Frankenstein:

There was a recent post on Database Administrators, where there was some confusion over Daylight Saving Time(DST) in Australian timezones. Let’s be honest though–timezones are confusing, especially when DST comes into play. Arizona Time doesn’t observe Daylight Saving Time, so sometimes it’s the same as Mountain Time, and sometimes it’s Pacific Time.

Finding the right time zone can be hard. As of this writing, Windows recognizes 141 different timezones, many of which overlap.

In this case, the asker was running this query, and confused over why it wasn’t properly reflecting DST, and the other columns in the DMZ weren’t helpful either:

This is why everybody should just use Eastern Standard Time: it’s the standard—says so in the name, even.

Comments closed

Relative-Time Slicers in Power BI

Gerhard Brueckl wants your Power BI reports to know what day it is:

A very common requirement for a Power BI report that I stumble across at almost all of my customers is to automatically show data for the current day/month/year when a report is opened. At first sight this seems like a very trivial problem but once you dig into the problem, you will realize that all of the common solutions out there have some disadvantages and only solve the problem partially.

Click through for a clear description of the problem and a clear description of the answer.

Comments closed

Rounding Times in SQL Server

Steve Stedman has the low-down on time rounding in SQL Server:

One thing that I end up having to look search on regularly is rounding of dates and times in Transact SQL, having looked this up too many times I finally realized that it is time for me to do my own blog post for it.

First off, whats the difference between rounding and truncating in these examples. Rounding rounds to the closest second, so 10:00:31 is rounded up to 10:01:00, and 10:00:29 is rounded down to 10:00:00. With truncation, it simple changes the truncated area to 0’s. so 10:00:31 gets truncated down to 10:00:00, and so does 10:00:59. Sometimes you may want rounding, and sometimes you may want truncation (floor) for your specific needs.

After having used date_trunc() in Postgres, I’d really like something similar in SQL Server.

Comments closed

Representing Dates in Power BI: Date or Integer?

Marco Russo and Alberto Ferrari share their take on a classic debate:

A question that is often asked during the design of a Power BI data model is whether it is better to use an Integer or a Datetime column to link a fact table with the Date dimension. Historically, using Integers has always been a better choice in database design. However, Tabular is an in-memory columnar database, and its architecture is quite different from the relational databases we might be used to working with.

Indeed, in Tabular there are no technical differences between using a Datetime or an Integer to create a relationship. The database size, the query speed, and any other technical detail are absolutely identical. Therefore, the choice is not related to technical aspects, but rather on the convenience of the design. Depending on the specific requirements of your model, you might favor one data type against the other. In the most common scenarios, a Datetime proves to be better because it provides more possibilities to compute values on dates without having to rely on relationships. With that said, if your model uses Integers and you do not need to perform calculations on the dates represented in the table, then you can choose the most convenient data type – that is, the one already used in the original data source.

The remaining part of the article aims to prove the previous sentences, and to provide you with the technical details about how we tested the respective performance of the two options.

Click through for Marco and Alberto’s analysis, noting that “date” here does not include time of day, so it would have the same cardinality as the integer date key. This was a more important thing fifteen years ago, before columnstore technologies (like columnstore indexes and VertiPaq) were readily available and that 4-byte integer was considerably smaller than an 8-byte DATETIME.

Comments closed

Creating a Time Zone Dimension

Jonathan Kehayias has a follow-up to a prior post:

The basis for the approach in this post is going to be the TimeZoneInfo class from .NET, which is “supposed” to be up to date with any time zone DST rules from the Windows Registry. I used a modification of the PowerShell example in the .NET Docs page for the TimeZoneInfo class to build a a flat file that is then bulk loaded to a table in SQL Server using BCP with all the time zone adjustment rules:

There’s a lot of effort in here, but as Jonathan notes, it will provide you the ability to get the right time zone as of a particular date and time. Or at least, maybe—I’m not sure if the Windows registry includes historical time zone shifts such as when the US changed when daylight savings time starts and ends.

Comments closed

Performance Issues with AT TIME ZONE

Jonathan Kehayias lays out a warning:

This is not the type of blog post that I enjoy writing. First let me start off by saying this is not a bash against Microsoft, the SQL Server team, or anything other than an informative post to let people know that there is a potential performance limitation in a very useful feature of SQL Server 2016 and higher. The AT TIME ZONE syntax was added in SQL Server 2016 to handle changing datetime values from one time zone to the offset of a different time zone using string names for the time zone. This is a great feature that simplifies converting datetime values but there is an unfortunate draw back to the implementation; it relies on the time zones that are stored in the Windows Registry and therefore has to make calls out to Windows which unfortunately occurs row-by-row for large result sets when used inside of a query.

Read on for more detail, as well as the way that Jonathan fixed the performance issue.

Comments closed