Time Zone problem when extracting data from Teradata

Options
UserBird
UserBird Dataiker, Alpha Tester Posts: 535 Dataiker

Hi,

In my project , I have to load data from teradata to dataiku using sql request.

The problem is in the date format, because it's automaticaly parsed by dataiku, and adding time zone, for Example : 2015-10-12T22:00:00:000Z, OR 2015-10-12T23:00:00:000Z , and in my dataset, when I extracted day_of_week from date, I noted that all dates were offbeat.

I tried to change the parameters : Assumed Time zone AND Teradata Time zone, but it still gave me the same result.

Thanks in advance for your attention and help.

Answers

  • Clément_Stenac
    Clément_Stenac Dataiker, Dataiku DSS Core Designer, Registered Posts: 753 Dataiker
    Options
    Hi,

    If, in the Teradata server, you have configured the "assumed time zone" to Paris, then this is normal:

    Date fields in teradata don't carry a timezone, so you have to provide the "timezone we assume they are representing". Thus 2016-07-02 will be assumed to mean "2016-07-02 at midnight in Paris", which is indeed "2016-07-01T22:00:00Z" (Z = Zulu = UTC)

    By reformatting the date in a preparation recipe, you can choose to rewrite it in another timezone, in which case it could become 2016-07-02T00:00:00+0200 (since midnight in Paris is 2am UTC)

    Hope this helps
  • Clément_Stenac
    Clément_Stenac Dataiker, Dataiku DSS Core Designer, Registered Posts: 753 Dataiker
    Options
    Also, when extracting date components, you can choose in which time zone to extract. Thus, 2016-07-01T00:00:00Z will yield day=01 in UTC, but day=02 in Europe/Paris
  • mak
    mak Registered Posts: 1 ✭✭✭✭
    Options
    HI, thank you for your response. In fact I did what you said, and all dates were incremented by one day, bu infortunately, it still didnt match with the original dates in teradata, so I did some manipulations and added one more day after the modification of the time zone which added one other day, total 2 days added and then tested with query in teradata first, and in dataiku after extraction and the samples were the same. So it was amazing for me this difference of two days, and asked if the problem is only came from time zone.

    Thanks for your help.
Setup Info
    Tags
      Help me…