NetSuite SuiteAnalytics Timezone Issues

I am trying to pull in transaction data from NetSuite via the SuiteAnalytics connector. For whatever reason, DateTime values are stored in GMT/UTC (specifically a custom timestamp field) in NetSuite and the Date values (specifically the TRANDATE field) is stored in our local Pacific timezone.

When I preview the dataset returned from my query, it (erroneously?) tries to adjust ALL date columns for the different timezones. For example a transaction date 2017-07-31 00:00:00 will preview as 2017-07-30 05:00:00PM. It will also try to correct the timezones when use the dataset on a card. The problem here is that my date columns are for the date before, but my timestamp columns are accurate.

When I export the dataset or use it within an ETL (MySQL, Redshift) it doesn't make any adjustments for timezones.

Has anyone else worked out a best practice for how to handle these? I have come up with two "solutions" that are less than ideal:

Correct the timezone for DateTimes in the SuiteAnalytics query, then pass all NetSuite datasets through an ETL before using them on cards. Anyone who mistakenly previews/uses the SuiteAnalytics dataset directly will see incorrect numbers.
Add 7 hours to the Date columns in the SuiteAnalytics query, then anytime it's used in an ETL be sure to subtract 7 hours from all Date/DateTime columns.

I'm leaning more toward the second solution, but this is all so unintuitive I'm afraid it will be hard to maintain or changed in a bug fix.

Has anyone dealt with this or a similar problem?

Find more posts tagged with

Comments

gwsv-it

Casting as Date prevents the timezone correction for Date fields. The DATE() function (and just regular date columns) in SuiteAnalytics return DateTimes and need to be explicitly cast as dates. I'm still not sure I understand how they're handled in different transforms yet, but at least that is good to go.

Thanks to ckatzman for his post on timezone issues from a couple of years ago.

DDalt

I have found myself continually coming back to timezone issues, particularly when trying to reconcile our Ecommerce sales which happen at all hours of the day.

After reading your second comment, it seems that a good solution regarding data sets going forward would be to create a second date column that casts the original date column as DATE. This way, you have the original hourly data that can be tranformed using BeastMode and then also a column that gives the ability to see daily data without the headache of dealing with timezone conversions and daylight savings time. Has this been your approach since the original post?

gwsv-it

Sorry for the slow reply.

We've been casting as date before the data gets to Domo wherever possible. Some of our "omnichannel" E-commerce systems store the datetime in the whatever timezone the marketplace uses which made it even more confusing. We would only preserve the time (and make the timezone correction) when it's critical for building reports (like retail store sales).

I like your idea of adding a second column, especially if you've got a lot of power users who might want to drill down to the data and see the timestamp. We don't have too many power users here so we try to simplify whenever possible.