Kurro Lopez · Mar 4 4m read

How to become a time lord - Time travel

Time travel is like visiting Paris. You can't just read the guide, you have to throw yourself into it. Eat the food, use the wrong verbs, get double the charges, and end up kissing complete strangers.

The Doctor

We are now going to travel through time, that is, we are going to see future and past dates and how to calculate them in different formats. The TARDIS doesn't wait, take the controls and hold on tight.

Travel in TARDIS

Let's go to tomorrow

as we saw in the previous article, The date in internal format is divided into the number of days since January 1, 1841 and the number of seconds since 00:00 hours.

If we want to increase a day, we can think... by adding a value or subtracting it from the date part... I'm already traveling through time.

SET InternalDate = $ZDATEH(Today, 4)
SET Future = InternalDate + 2 
SET FutureDate = $DATE(Future, 4)
WRITE "Today: "_Today,!,"Internal date: "_InternalDate,!,"Future: "_Future,!,"Future date: "_FutureDate
> Today: 02/09/2022
Internal date: 66149
Future: 66151
Future date: 04/09/2022

How easy... you know how to move through the days. But if you want to go the following month, things change. The calendar does not have the same number of days from one month to another, so it is not worth adding 30 days to the date. Let me introduce you to our command to add or subtract time parts.

If you are a T-SQL user you'll see that the command will sound quite familiar to you: DATEADD.

It's part of the library $SYSTEM.SQL.Functions


Back to the future

We can add months, days, years... or remove it from the indicated date.

SET InternalDate = $ZDATEH("09/02/2022", 4)
SET Future = $SYSTEM.SQL.DATEADD("mm",1,InternalDate)
WRITE "InternalDate "_InternalDate,!,"Future: "_Future
InternalDate: 66149
Future: 2022-03-09 00:00:00

If we want to remove days, months and years we would have to do it in parts, each of the steps separately

Set InternalDate = $ZDATEH("09/02/2022", 4)
Set PastDays = $SYSTEM.SQL.DATEADD("dd",12,InternalDate)
Set PastMonths = $SYSTEM.SQL.DATEADD("mm",8,PastDays)
Set PastYears = $SYSTEM.SQL.DATEADD("yy",-7,PastMonths)
WRITE PastYears
2015-10-21 00:00:00

Mc Fly, Are you there??


It's my birthday right now, or not...

If what we want is to know what is the difference between two dates, we will use the command DATEDIF.

Set internalDate = $ZDATEH("09/02/2022", 4)
Set myBirthday = $ZDATEH("01/02/2023", 4)
Set days = $SYSTEM.SQL.DATEDIFF("dd",internalDate, myBirthday)
WRITE !days," days left until my birthday"

> 357 days left until my birthday

Well, look how good... we already know how to see the difference between two dates by days, months, years, etc...


I want to see Christopher Columbus discover America

Well, as we have said, our time format has the value 1 for 01/01/1841, and Christopher Columbus arrived in America on Oct. 12th 1492, so you will think... well, I am putting negative values to the date of 1 up to 1492



Opss... Houston, we have a problem. We can't use negative values to get a date before 1841. Well, I do the conversion from text date to internal format and see what it returned.

WRITE $ZDATEH("12/10/1492",4) // European format


MMMEEEECCC !!! Thanks for competing, but it is not the correct answer.
How can we solve it?

This is because the dates have a defined minimum value that prevents going further, so if we use the mindata parameter it will allow us to use negative values in the internal format.

WRITE $ZDATEH("12/10/1492",4,,,,,-672045)
> -127184

The value that has been placed in the 7th position of the parameters indicates that the minimum accepted value will be -672045, to satisfy your curiosity, it corresponds to 01/01/0001, it is also the minimum number that we can use.

If you find that you get lost when counting "commas" to add this parameter, you can configure the National Language Support, NLS, and you leave it active.

SET originalValue= ##class(%SYS.NLS.Format).GetFormatItem("DateMinimum")
WRITE originalValue,!
DO ##class(%SYS.NLS.Format).SetFormatItem("DateMinimum", -672045)
WRITE $ZDATEH("12/10/1492", 4),!
DO ##class(%SYS.NLS.Format).SetFormatItem("DateMinimum", originalValue)  // Return to the minimum value originally established

> 0

Then, you can now travel through time without problems.

Next article: Universal time



Thanks to @Robert Cemper for his article on negative $Horolog, which has helped me to complete this article and which you can see in the following link

0 132
Discussion (5)1
Log in or sign up to continue

Nice article. Didn't know the easy way round negative dates.

But how does it handle the English calendar riots of 1752 when we went from September 2nd to September 14th overnight?

It returns a value of -32438 for September 3rd 1752 which didn't exist in England which is where I am now. Someone needs to fix this quickly!

You are right.

In Europe we have the same problem when started the Gregorian calendar.

October 4th 1582 was in Julian calendar (thursday) and the next day (friday) was October 15th 1582...

If you want to retrieve the October 10th 1582... in theory that day never existed

Hi @Stuart Strickland !

This well-known effect is named Proleptic Gregorian Calendar.
A bunch of other DBs is using it as well. As well as my favorite competitor O*****.

This means it calculates backward as if we always had the Gregorian date since the beginning
and the lost days in  October 1582 and other adjustments are ignored.
In fact, at that time almost every country and smaller had its own calendar. Often like "in the 3rd month of the 7th year of the reign of Duke Goofy 4th".  And converting regional dates from Spain to England or Franc or Italy or Ireland or even Scandinavia is a special mathematical exercise for students of History. On top of that, you often don't have the information if they counted years by Sun calendar or by Moon calendar.


It is worth mentioning that the "DATEDIFF" function does not always calculation as expected, eg:

>w $system.SQL.DATEDIFF("yy",$zdh("31.12.2022"), $zdh("01.01.2023"))
>w $system.SQL.DATEDIFF("mm",$zdh("31.12.2022"), $zdh("01.01.2023"))

This is stated in the documentation for SQL DATEDIFF