COALESCE Function Rigidity when working with DATE/TIME


#1

I’ve been creating some views lately where I have encountered what I consider unreasonable rigidity with the COALESCE Function when using DATE and TIMESTAMP functions. Let me give some examples.

Here is an example that I think works well. Note that Date_Opened type is of type DATE and NOW() returns a TIMESTAMP.

select Case_Open.Case_Number,DATEDIFF(‘DAY’, Case_Open.Date_Opened, NOW()) as AGE FROM Case_Open

And here is the same thing but using COALESCE and you can see the exception given.

select Case_Open.Case_Number, COALESCE(Case_Open.Date_Opened, Now()) FROM Case_Open
Exception: expressions in ELSE clause must be of the same or compatible types as those in the THEN clauses.

It seems odd to me that COALESCE would be this sensitive between a DATE and TIMESTAMP.

My desired statement would be something like this:

DATEDIFF(‘DAY’, Case_Open.Date_Opened, COALESCE(Case_Close.Date_Closed, Now())) as AGE

The logic being:
Take the beginning date
Take either the ending date or the current time
Give the difference in days

If anyone has a workaround I’d appreciate it.

I’ve tried to include a CAST but with the same result.

DATEDIFF(‘DAY’, Case_Open.Date_Opened, COALESCE(Case_Close.Date_Closed, CAST(Now() as DATE))) as AGE


#2

Hi @davidrubert -

The behavior you are experiencing is due to the mismatch of data types, which MapD has to be pretty strict about in order to run on the GPU.

With DATEDIFF, the internal code knows that it makes sense for either a DATE or TIMESTAMP comparison, and it returns an INT. For COALESCE, only one type can be returned; the first type will be DATE, and possibly TIMESTAMP when missing. In this case, we don’t try to anticipate what the different type combinations might be, as having type promotion rules could be pretty complicated.

For your COALESCE statement, making the DATE_CLOSED into a TIMESTAMP or doing a CAST on Now() will fix your problem. Since your source data are DATE, I suspect casting Now() to DATE makes more sense.


#3

Thanks for the fast response Randy.
I think your answer and my edit crossed paths actually.
I tried CASTing Now as DATE and it still barfed. I’ll double-check my syntax and also try CASTing my DATE and TIMESTAMP to see if that works.

Thanks again.


#4

No problem, glad to help.

I should’ve been a bit more precise, colloquially a CAST but in mapdql DATE_TRUNC(date_part, timestamp)


#5

Or EXTRACT(date_part FROM timestamp) for that matter. Either one should get you what you need.


#6

Thanks. Sorry if I’m a bit of a nub :wink:


#7

It’s no problem at all, thanks for checking us out! We’re constantly working on improving our documentation and creating content, so this post will help anyone having this same issue in the future.

If there’s anything else we can help you with, please feel free to keep creating new threads, we’re all happy to help.


#8

Extract example to get the equivalent of a DATE type?

Do I extract year and month and day?


#9

Sorry, I’m making a proper mess out of this. Should’ve opened up a session first :frowning:

mapdql> select cast(now() as DATE) from <table> limit 1;
EXPR$0
2018-06-11

Can you pass this into the COALESCE and have it work?


#10

It doesn’t appear to work:

select Case_Open.Case_Number
, DATEDIFF(‘DAY’, Case_Open.Date_Opened, COALESCE(Case_Close.Date_Closed, cast(now() as DATE))) as AGE
FROM Case_Open JOIN Case_Close on Case_Open.Case_Number = Case_Close.Case_Number;
Exception: expressions in ELSE clause must be of the same or compatible types as those in the THEN clauses.


#11

I also tried to CAST the other two DATEs as TIMESTAMPs and got a different error: Invalid Cast.

SELECT Case_Open.Case_Number
, DATEDIFF(‘DAY’, Cast(Case_Open.Date_Opened as TIMESTAMP), COALESCE(CAST(Case_Close.Date_Closed as TIMESTAMP), now())) as AGE
FROM Case_Open
JOIN Case_Close on Case_Open.Case_Number = Case_Close.Case_Number;

I tested each of the other two CASTs individually and they work fine. It is the combination with COALESCE that I just can’t get to work.

And again, I really appreciate your help.


#12

Thanks for your patience @davidrubert. At this point, I’m not sure you haven’t uncovered a bug here. I’m trying these comparisons on a table I have and they don’t seem to be working.

I’ll file a bug report and see if I can get an engineer to take a look at this ASAP. It feels like it should be working, hopefully there isn’t something weird we’re missing.


#13

Cool and thanks again.


#14

And perhaps we shouldn’t forget the alternate approach of modifying the EXTRACT statement so that you can extract a DATE from a TIMESTAMP


#15