nondeterministic writing of dates in the database

Marek_Zak
Marek_Zak Registered Posts: 4 ✭✭✭

Hello
I have a problem with unclear date conversion in Dataiku.
The scenario is as follows:

* Input table (postgresql) have column with data in text format (column type is varchar(32))

eg. content: 03-01-2019

* Next step is a SQL recipe:

select

TO_DATE(input_column, 'MM-dd-YYYY')::timestamp with time zone as output_column

from my_input_table

* Result is saves in my_output_table

It works but.

In one row date '03-12-2020' is saved like this: 2020-03-12 01:00:00.000

In second row date '05-01-2020' is saved like this: 2020-05-01 02:00:00.000

I do not understand why in one column some rows have different time zone than others ?!?!

Best Answer

Answers

  • louisplt
    louisplt Dataiker Posts: 21 Dataiker

    Hello @Marek_Zak
    ,

    My guess would be that this 1 hour difference comes from the daylight saving time. This explanation works for the two rows you shared.

    Hope this helps

  • Marek_Zak
    Marek_Zak Registered Posts: 4 ✭✭✭

    Thanks for the clarification.
    Now, can I somehow change this logic? Can I turn off this mechanism somewhere? There are no such surprises when doing such conversions in SQL from the database level. However, when this SQL goes through Dataiku, magic starts to happen.

  • louisplt
    louisplt Dataiker Posts: 21 Dataiker

    If you want to avoid this behavior I suggest you remove the option "::timestamp with time zone" in your query.

  • Marek_Zak
    Marek_Zak Registered Posts: 4 ✭✭✭

    It would be great if I could do it, but Dataiku doesn't know "date" SQL type. When creating a column in the database, the "timestamp with time zone" type will do there anyway, imposing time zones for each value at its own discretion.

Setup Info
    Tags
      Help me…