Subscribe to this thread
Home - General / All posts - Calculate duration between two dates
LeRepère121 post(s)
#15-Jun-18 19:11

I just browsed the help and the forum and I did not find anything on how to calculate the number of days between two dates in M9 using SQL or a function.Is this possible in the current version of M9?

tjhb

8,227 post(s)
#16-Jun-18 04:28

I was expecting that this would be as simple as subtracting one DATETIME value from another.

But it seems the minus operator (-) is not yet implemented in SQL9. Nor is plus (+).

--SQL9

CAST('17/6/2018' AS DATETIME) - CAST('16/6/2018' AS DATETIME)

--> '-': Type mismatch.

On the other hand, < and > are already available.

We should request that the -/+ operators be wired up for DATETIME.

Currently a small script function is needed.

Dimitri


4,991 post(s)
#16-Jun-18 06:11

Datetime is not a scalar type. It is a vector type: "A calendar date (day, month, year) and time (hour, minute, second, millisecond) ranging from January 3, 0001, 00:00:00.000 to December 31, 9999, 23:59:59.999"

If you want to know the difference in days between one datetime value and another datetime value, you first convert both datetime values into scalars, the number of days in that date, and then you can use the subtract operator "-" that works with scalars.

Ignoring components other than day, month and year, a datetime value of 17/6/2018 can be approximately converted into a scalar integer value as number of days as

17 + 6 * 12 + 2018 * 365

You can get the year, month, and day as respective integer values from a datetime using functions such as DateTimeMonth(<date>).

I write "approximately" because the simple expression above neglects to correct for leap year and leap century. You don't care about that if your date calculation is within the same year, in which case you can compute using month and day components, or doesn't cross a leap year or leap century boundary, in which case the leap year/century errors wash out as common to both dates.

As to how all the above can be handled, Transact-SQL has a DATEDIFF function that allows you to specify what "boundary" part of a datetime is crossed. DATEDIFF handles leap years correctly but I don't know about leap centuries. It's hard to believe they would have missed that.

In principle, handling leap years and leap centuries in straight SQL without DATEDIFF is not complicated. It just leads to lengthier queries. See, for example, the discussion in

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0cb77805-f71f-4e53-8d5d-c905786836b0/leap-year-calculation-logic-by-sql?forum=transactsql

One solution for datetime calculations in 9 would be to implement a SQL Server style DATEDIFF function, ensuring, of course, that it is aware of leap centuries as well as leap years.

tjhb

8,227 post(s)
#16-Jun-18 06:46

(1) Yes, although the fact that < and > seem to work correctly with DATETIME types already seems to suggest that there is implicit conversion to scalar under the hood? (Weakly suggests, I would agree. Maybe only hashes are being compared. I haven't tested enough to be sure.)

(2) We can't currently CAST a DATETIME value to an integer type in SQL9. I tried--I think we should be able to. (Then do the subtraction, then CAST back, and so on.)

(3) DATEDIFF would be fine. It would return a DATETIME result, from which we could extract month, day, hour, second as needed, using existing SQL9 functions.

(4) But instead of DATEDIFF, why not just build that in, using arithmetic operators?

Dimitri


4,991 post(s)
#16-Jun-18 06:59

We can't current CAST a DATETIME value to an iteger type in SQL9

That CAST would have to be to or from the total number of milliseconds since the "beginning of time", the origin being January 3, 0001, 00:00:00.000. You could then do whatever computations you wanted based on that integer and use modulo computations to extract differences in years, months, days, etc.

Really, the vector representation of a datetime as consisting of years, months, etc., is just a different way of extracting the various components from a scalar that is the number of milliseconds since the origin, so it seems right that < and > work, since those results are Boolean and dimensionless. But - and + involve asking what the result should be... the difference in milliseconds given arithmetic done on an implied CAST into total milliseconds? You'd have to have a way of specifying what component interests you, or to write lengthier queries. The advantage of using DATEDIFF is that is all well understood with many examples of how to do what you want.

Speaking of CASTs into millisecond measurement, that would raise the additional complication of extra time units, seconds or milliseconds, inserted over the centuries in an ad hoc manner to correct calendar drift or slowing Earth rotation. Leap centuries are easy to reckon using a pair of simple modulo 100 and modulo 400 expressions, but ad hoc additions are, well, ad hoc. I don't know if the current Microsoft datetime system that Manifold utilizes correctly reckons them. You don't care about that if you are measuring day differences, of course, but if you are going to CAST into milliseconds across thousands of years it seems the right thing to do to note even minor adjustments.

tjhb

8,227 post(s)
#16-Jun-18 07:10

For .NET, I think the "beginning of time" is 1 January 1970?

(C++ is very different though?)

The range of built-in operators on the .NET DateTime structure seems ideal, if that could be done in SQL9.

[By the way because of your earlier helpful post I also tried CASTing SQL9 DATETIME to FLOAT64. Again gives "datetime -> float64: Cannot convert types."]

LeRepère121 post(s)
#16-Jun-18 18:04

Thank you for your specifications and suggestions.It's appreciated.

I considered the possibility of doing the calculation from the Julian days.The attached file compares the method suggested by Dimitri with those two Julian day calculations according to two sources.The results are not convincing in any case.I certainly missed something in my integration work in the SQL query.

Attachments:
NbJour_2Dates.map

tjhb

8,227 post(s)
#16-Jun-18 23:15

The problem is not this, since your date strings happen to be in US format.

But are you sure you want to be calculating modern dates (after 1582) using the Julian calendar? Not Gregorian?

tjhb

8,227 post(s)
#16-Jun-18 22:37

For .NET, I think the "beginning of time" is 1 January 1970?

I got that completely wrong.

DateTime.Ticks:

represents the number of 100-nanosecond intervals that have elapsed since 12:00:00 midnight, January 1, 0001 (0:00:00 UTC on January 1, 0001, in the Gregorian calendar)... It does not include the number of ticks that are attributable to leap seconds.

As Dimitri said.

By the way I think this implies that leap years and leap centuries are included.

Wikipedia says that there have been 27 leap seconds added to UTC since 1972 (and gives a schedule of when). 5 of those 27 seconds have been added since 2000. When comparing .NET dates between different years, if sub-minute accuracy is required, then we need to splice in these adjustments manually.

DateTime.Ticks is just a 64-bit integer. So I do think that in principle we should be able to CAST an SQL DATETIME value to INT64 (i.e. a Ticks value), do simple arithmetic, and CAST back to DATETIME.

Except for the caveat about leap seconds... which alone may mean that a built-in DateDiff() function would be a better idea, again as Dimitri says. (I am also assuming direct equivalence between DATETIME in SQL9 and DateTime in .NET, which may be incorrect.)

tjhb

8,227 post(s)
#17-Jun-18 03:02

Sometimes it pays to read (or search, or remember) the forum!

Adam has already answered this in depth (to a question by Art), with a helpful script.

I found it when looking for something else. Oops.

Thanks Adam!

tjhb

8,227 post(s)
#17-Jun-18 04:04

It's easy to adapt Adam's query and script to use two static dates, rather than one static date and "now". If you need help just say.

I think the difficulty

We didn't do this yet because the first function [a query function to return the current date / time] needs special handling in queries (it has to cache whatever it returned, semantically).

only applies when "now" is one of the arguments. Two static date-time values would not need caching.

LeRepère121 post(s)
#17-Jun-18 17:03

Thanks tjhb.It works well.Here is the adatation that I made of the code of adamw.

// C#

// Adaptation du script de adamw -- http://www.georeference.org/forum/t136681.3#136693

class Script

{

public static double DaysFromToday(System.DateTime p, System.DateTime r)

{

return (r - p).TotalDays;

}

static Manifold.Context Manifold;

static void Main()

{

}

}

When comparing the results with those of the other functions of the M9 file initially transmitted, we nevertheless note a great similarity with the results of the calculations of the functions JJ1 and JJ2, with the exception of the last case of the table which represents 1 day of end of'year.

Thanks again

Attachments:
TableauComparaison.JPG

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.