A Fine Slice Of SQL Server
Ajay Dwivedi has a function to build out time duration as a string:
As DBA, there are various scenarios where I display duration in results. More often, for visual effects, I like to convert the same duration into [DD hh:mm:ss] format.
For example, for representing the duration of total waits, resource waits & signal waits for wait types from [sys].[dm_os_wait_stats].[wait_time_ms] on Grafana dashboard by converting to [DD hh:mm:ss] format:-
Click through for the function.
Comments closedEd Hansenberry adds useful information to a Power BI report:
It is often useful to tell your users when the report refreshed so they have some idea of how current, or stale, the data is. Unfortunately, you cannot just add a TODAY() formula to your report as that will change each time they open the report. This method will give you the refresh time stamp and compensate for Daylight Savings Time.
This frustrates me a bit—the time when the report was generated is really easy to do in Reporting Services and is certainly a good practice to follow when building reports, and yet there’s a multi-step process involving writing M code to do something which ought to be trivial.
Comments closedMaxim Gekk, et al, look at the different date and time data types in Apache Spark 3.0:
The definition of a
Dateis very simple: It’s a combination of the year, month and day fields, like (year=2012, month=12, day=31). However, the values of the year, month and day fields have constraints, so that the date value is a valid day in the real world. For example, the value of month must be from 1 to 12, the value of day must be from 1 to 28/29/30/31 (depending on the year and month), and so on.These constraints are defined by one of many possible calendars. Some of them are only used in specific regions, like the Lunar calendar. Some of them are only used in history, like the Julian calendar. At this point, the Gregorian calendar is the de facto international standard and is used almost everywhere in the world for civil purposes. It was introduced in 1582 and is extended to support dates before 1582 as well. This extended calendar is called the Proleptic Gregorian calendar.
Starting from version 3.0, Spark uses the Proleptic Gregorian calendar, which is already being used by other data systems like pandas, R and Apache Arrow. Before Spark 3.0, it used a combination of the Julian and Gregorian calendar: For dates before 1582, the Julian calendar was used, for dates after 1582 the Gregorian calendar was used. This is inherited from the legacy
java.sql.DateAPI, which was superseded in Java 8 byjava.time.LocalDate, which uses the Proleptic Gregorian calendar as well.
Even in this three-paragraph snippet, you can already get a feeling for how complex working with dates can be. Then throw in the complexities of time and you get a detailed post full of good information.
Comments closedTeo Lachev has some tips on working with time zones in Power BI:
A client has Power BI models connected to Dynamics Online. Dynamics stores all dates in UTC instead of keeping the time offset, such as 7/14/2020 1:21:29 AM +00:00. Naturally, the users want to see dates localized to the US Eastern Time zone. Easy, right? Use the Power Query ToLocal time transformation (in the Transform ribbon, expand Time, and then click To Local) to offset with the desired number of hours. But there are a few issues with this approach:
Read on to understand why this approach won’t cut it, and to get one which will.
Comments closedMatt Allington follows up on a previous post:
Then, sometimes (like this time) I discover that someone has a better way to solve the same problem that I shared on my blog. This is what happened last week after I shared my first article about how to calculate the total business hours between 2 date/time stamps. I shared the way I solved this problem last week, but one of my readers, Daniil Bogomazov, shared a brilliant alternative solution to the same problem. The solution is so good that I am sharing his solution with you here today.
Read on for a clever solution and a detailed comparison to Matt’s prior answer.
Comments closedI wrote some other posts on this topic in the past, you can find them here and here. In the first post I explain how to create “Time” dimension with time bands at minutes granularity. Then one of my customers required the “Time” dimension at seconds granularity which encouraged me to write the second blogpost. In the second blogpost though I didn’t do time bands, so here I am, writing the third post which is a variation of the second post supporting time bands of 5 min, 15 min, 30 min, 45 min and 60 min while the grain of the “Time” dimension is down to second. in this quick post I jump directly to the point and show you how to generate the “Time” dimension in three different ways, using T-SQL in SQL Server, using Power Query (M) and DAX. Here it is then:
Click through for the code, which includes several sample bands (e.g., 5 minutes, 15 minutes) that you can also control.
Comments closedMatt Allington combines DAX and a calendar table to calculate business hours:
I was helping a client this past week to calculate the total business hours between a start date/time and an end date/time, taking into account the working days, public holidays and non-working weekends, etc. As is often the case, I thought it would be a great blog article that I could share with my readers. This is a pretty involved problem with lots of moving parts, so as such I have decided to record a video showing you how I solved the problem, 1 step at a time.
Click through for the video as well as a description and the code.
Comments closedWhen I first started looking at the calculation groups and changing the currency formats, I thought that my existing currency format was correct. Boy was I wrong and once I found that out and corrected it, my Currency Format Strings started working.
As per the Microsoft documentation found here Dynamic format strings for currency conversion I had to make sure that my Currency format followed the following pattern.
Read on for an example and demonstration.
Comments closedRandolph West covers the internals of how date and time data types are stored in SQL Server:
DATEis the byte-reversed number of days since the year 0001-01-01, stored as three bytes. It goes up to 9999-12-31, which is stored as0xDAB937. You can check this value by reversing the bytes and sticking them into a hex calculator.37 B9 DAequals 3,652,058, which is the number of days since 0001-01-01.If you try to cast
0xDBB937as aDATEvalue (by incrementing the least significant bitDAby 1), it will throw a conversion error. There is obviously some overflow detection that protects against corruption in a date type.
Randolph looks at DATE, TIME, DATETIME(2), and DATETIME and explains how each is storedon a page.
Brett Powell shows how you can display a report’s execution time in a particular time zone:
For reports being viewed by users around the world, simply modifying the footer text box expression to note that this time is UTC may be a sufficient. However, for many paginated reports the users are all in one time zone and some of these users may ask to have the time zone conversion handled within the BI solution. The example in this post targets this scenario.
Even if the report serves users in multiple time zones, it’s technically feasible to leverage the UserID global field and a simple user to time zone mapping table to provide a local report execution time to all users. However, I tend to think most projects would not want to commit the time/resources for this logic – UTC date/time is what the users would get.
If you do need local report execution time, Brett has you covered.
Comments closed