Press "Enter" to skip to content

Category: Dates and Numbers

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

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).

Leave a Comment

Working with Calendar Tables

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

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

The Performance Cost of AT TIME ZONE

Erik Darling shows that AT TIME ZONE does not scale well when used in filters against columns:

Databases really do make you pay dearly for mistakes, and new linguistic functionality is not implemented with performance in mind.

I’ve written before about how to approach date math in where clauses: Where To Do Date Math In Your Where Clause

And it turns out that this lesson is brutally true if you need to pass time zones around, too.

Read the whole thing. In this respect, AT TIME ZONE is similar to pretty much all other date operators and functions.

Comments closed


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.

Comments closed

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.

Comments closed

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