search

rockset / community

Rockset community content
Apache License 2.0
16 stars 6 forks source link

DATE_TRUNC('QUARTER', date_field) function is not returning right values #19

Closed pamboo80 closed 3 years ago

pamboo80 commented 3 years ago

Hi Team,

There is some error in DATE_TRUNC SQL function.

It is not returning the right values. I have tested for 'QUARTER' and 'WEEK' datepart. It is returning right values for few and wrong for other dates. An example query is here.

SELECT DATE_TRUNC('week',CAST('1974-03-17' as DATE)),DATE_TRUNC('week',CAST('1974-03-16' as DATE)),DATE_TRUNC('week',CAST('1974-05-03' as DATE)), DATE_TRUNC('QUARTER',CAST('1974-03-17' as DATE)),DATE_TRUNC('QUARTER',CAST('1974-05-03' as DATE))

Results as in console: "1974-03-17", "1974-03-10", "1974-04-28", "1974-04-01", "1974-04-01"

But the right results are "1974 -03-11", "1974 -03-11", "1974-04-29", "1974-01-01", "1974-04-01"

I feel the 'WEEK' datepart is working right as Rockset DB consider 'Sunday' as the start of the week (17th March 1974 was a Sunday) . Added it just for the team to cross-check. But I feel there is some issue with the 'QUARTER' datepart. Please look in to this. Probably similar to quarter calculation bug fixed recently.

P.S: Attached is the resultset from a Postgres DB.

Postgres DATE_TRUNC Output

Thanks.

benhannel commented 3 years ago

The behavior for week truncation is intended. It is different than postgres, but it matches BigQuery's behavior. Just a matter of convention.

The behavior for quarter is definitely a bug. I'll look into that.

pamboo80 commented 3 years ago

Sure Ben. Thanks.

pamboo80 commented 3 years ago

Seems this is fixed in today's built. And also new SQL String functions CHR,ASCII, REVERSE which are requested too available in the current built. Thanks. @benhannel Can you please help me to verify if this issue https://github.com/rockset/community/issues/17 still on progress? (as still they fail from my side.) Let me know. Also we can close this issue. Thanks.

pamboo80 commented 3 years ago

Sorry @benhannel . https://github.com/rockset/community/issues/17 issue is fixed too. I have tested in console. Thanks.