TO_TIMESTAMP result differs from CAST as TIMESTAMP

1

I have a CSV file with datetime values like ‘2020-02-02 04:00:00.0000000’ when I try to convert/cast these to TIMESTAMPS I get really weird results. Sometimes the TO_TIMESTAMP return NULLs when CAST as TIMESTAMP returns the correct result.

Query:

SELECT 
    ContainerVisitID, 
    DepartureTime,
    LEFT(DepartureTime, LENGTH(DepartureTime) - 4) as DepTimeTrimmedText, 
    TO_TIMESTAMP(LEFT(DepartureTime, LENGTH(DepartureTime) - 4), 'YYYY-MM-DD HH:MI:SS.FFF', 1) as DepTimeToTimeStamp,
    CAST(LEFT(DepartureTime, LENGTH(DepartureTime) - 4) AS TIMESTAMP) as "DepTimeCast"
FROM "MCT Data Lake".raw.TOS.ContainerVisit
LIMIT 100

Result:

image
image1809×114 5.85 KB

2

@jeffreyslort The Dremio TO_TIMESTAMP takes up to milli seconds (FFF), that’s why you are getting null

https://docs.dremio.com/software/sql-reference/sql-functions/DATE_TIME/

3

Thanks for your reply.

I know it only takes timestamps up to milliseconds in precision. Thats why I remove all “additional” digits so we are left with a timestamp string with only 3 digits after the decimal point.

What I find very peculiar is that with these equally formatted strings TO_TIMESTAMP returns NULL 50% of the time while CAST AS DATE does not. While the documentation states that TO_TIMESTAMP should work the same as CAST AS DATE.

4

@jeffreyslort What version of Dremio is this?

5

@balaji.ramaswamy 20.1