Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
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 ?!?!
I think you have to specify the time zone in your query:
SELECT TO_DATE(input_column, 'MM-dd-YYYY')::timestamp at time zone 'UTC' as output_column
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
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. ๐
If you want to avoid this behavior I suggest you remove the option "::timestamp with time zone" in your query.
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.
I think you have to specify the time zone in your query:
SELECT TO_DATE(input_column, 'MM-dd-YYYY')::timestamp at time zone 'UTC' as output_column