Removing time from timestamp

1

How do I truncate a timestmp field to just date?

I tried cast(column as date), but it doesn’t work. The result is formatted as yyyy-mm-dd, but doing a DISTINCT or GROUP BY produces duplicate dates.

Strangely, cast(cast(cast(column as date) as timestamp) as date) does the trick, but I have to believe there is a better way.

2

Hi @Joe

Do you do group by on the original column?

Thanks
@balaji.ramaswamy

3

Yes, I’ve tried both a GROUP BY and DISTINCT. Here’s one query I tried:
select distinct
cast(start_time as date)
from
“xxx”.“xxx”.ads_event ae
fetch first 1000 rows only

This produces:
2017-07-09
2017-07-09
2017-07-09
2017-07-09
…

I checked the profile and found the SQL that Dremio is sending to Oracle:
SELECT * FROM (SELECT “EXPR$0” FROM (SELECT CAST(“START_TIME” AS DATE) “EXPR$0” FROM (SELECT * FROM “xxxx”.“ADS_EVENT” WHERE ROWNUM <= 2000) “ADS_EVENT”) “ADS_EVENT” GROUP BY “EXPR$0”) “ADS_EVENT”

It seems that Dremio is pushing down the “CAST … AS DATE” to Oracle. Since Oracle’s DATE includes time, the CAST has no effect.

The above is a query directly against the physical source. However I get the same result when querying a VDS and confirming that it’s using a reflection. Only difference is that the “cast(cast(cast(… as date)as timestamp)as date)” trick works with the VDS but NOT the physical source.

4

cast(date_trunc(‘DAY’,start_time) as date) seems to be what I need.