why are my transactions dates coming in as shift dates?

I have a pretty simple query pulling from Oracle tables but it's transforming the dates when I run it through the workbench.

In SQL Developer, it returns the Actual Completion Date as 'DD-MON-YY HH24:MI:SS':

to_char(gbs.actual_cmplt_date,'DD-MON-YY HH24:MI:SS') "Actual Completion Date"

I've used this same syntax to generate just a date so that I can roll up the transaction details:

to_char(gbs.actual_cmplt_date, 'DD-MON-YY') "Trxn Date"

When I run the query in Developer and sort by Trx Date, it gives me all the actual_cmplt_date transactions between 00:00:00 and 23:59:59. i.e. 18-MAY-16 from 00:00:00 - 23:59:59

As soon as I run it in Domo and sort by Trx Date, it returns actual_cmplt_date transactions based on shift values, i.e. 17-MAY-16 from 19:00:00 - 23:59:59 and 18-MAY-16 from 00:00:00 to 18:59:59.

Schema values are DateTime for the Actual Completion Date transactions and date for the Trx Date.

Any thoughts why it would be doing this? Thanks!!

Find more posts tagged with

Accepted answers

JCB76

Godiepi!

Thanks for the info - we were able to get around it by setting a time zone transfom on the job itself to EST.

That way, the transform tells the workbench what time zone the data is coming in as, so it can translate to UTC for processing. Then, the company settings tell the workbench what time zone it needs to upload the data as - in our case, EST again.

So, basically, the workflow ends up looking like this -

Import to workbench (EST) -> Workbench transform identifies the data as (EST) and converts to (UTC) for processing - > Workbench checks company settings and converts data from (UTC) back to (EST) prior to uploading to Domo.

Thanks again for your help ?

All comments

Godiepi

In workbench what time zone are you telling the data is in ? and in your Domo instance > company settings, what is the time zone selected ? and what is your actual data time zone ?

JCB76

In the workbench, I have not specified a time zone for this dataset. Does it default to the timezone set on the computer?

In Company Settings, it is set as America/New_York, which should also be EST, I believe.

We actually are actually transacting in an EST time zone.

Godiepi

Here it is what happens. your data before loading into Domo is in Eastern time then you don't need to specify the time zone in workbench (but workbench says UTC by defult) , now when the data makes it into Domo and you set your company settings time zone to Eastern time, what it does is adjust to eastern (but the data is already in eastern, that's why you are getting the shift). Our company went through this also and the way we solve was
1) our data is already in Eastern
2) then we don't set workbench to Eastern (meaning we did not touch that at all)
3) since the data is already coming into Domo as Eastern from Workbench, we set the company settings time zone to UTC (click reset , by default UTC) to prevent Domo from converting (-5 or -4 hours) the data again

kshah008

@JCB76, tagging you in case you haven't seen Godiepi's latest reply.

JCB76

Godiepi!

Thanks for the info - we were able to get around it by setting a time zone transfom on the job itself to EST.

So, basically, the workflow ends up looking like this -

Thanks again for your help ?