Press "Enter" to skip to content

Category: Dates and Numbers

Migrating DATETIME Columns to DATETIMEOFFSET

Josh Darnell makes a change:

We have an application that uses datetime columns in a number of places. All of the users have always been in Eastern Time, but now we have a request to introduce users from a different time zone (Central Time) into the system. The lack of time zone information in our dates and times now presents a problem.

The system needs to communicate to users how long ago something occurred, or a time in the future that something needs to be done. If an Eastern Time user enters in a “follow up time” of today at 2:00 pm, a Central Time user could log in, see that, and end up being an hour late following up with their customer.

Click through for the process and several bugaboos you might run into. What we’ve done was to force all application times in UTC in DATETIME or DATETIME2 format and then store user preferences on time zone in the application, translating from UTC to the relevant time zone at that level.

Leave a Comment

Date and Time Functions in Cosmos DB

Hasan Savran walks us through date and time functions in Azure Cosmos DB:

Json does not have datetime data type, you need to keep the datetime information in string. This can be a problem for database engines specially if user needs to search by date or sort by date. Cosmos DB team introduced bunch of datetime functions to the Azure Cosmos DB database engine this month. You can read my older post about DateTime in CosmosDB if you like to know how Azure CosmosDB saves the datetime in documents. I will cover the new datetime functions in this post. Here is the list of the functions 

Click through for those functions and how you can use them.

Leave a Comment

Building a Time Duration String

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 closed

Adding a Last Updated Time to Power BI Reports

Ed 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 closed

Dates and Timestamps in Spark 3.0

Maxim Gekk, et al, look at the different date and time data types in Apache Spark 3.0:

The definition of a Date is very simple: It’s a combination of the yearmonth 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.Date API, which was superseded in Java 8 by java.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 closed

Handling Time Zones in Power BI

Teo 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 closed

Another Way to Calculate Elapsed Business Hours with DAX

Matt 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 closed

Creating a Time Dimension with Time Bands in Power BI

Soheil Bakhshi shares how you can create a time dimension with a granularity of seconds in Power BI and SSAS Tabular:

I 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 closed

Calculating Business Hours with DAX

Matt 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 closed

Creating Currency Formatting Strings with Power BI

Gilbert Quevauvilliers walks us through formatting currencies via calculation groups in Power BI and Analysis services:

When 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 closed