Batch timestamp format

SpeedySteven · August 2022

I've created a number of DQ outputs running of a scheduled workflow to create a multibatch dataset of results over time. Unfortunately the batch timestamp although referenced as date/time, is not read as date time by excel or Power BI. Is there a format setting so that batch timestamp can be seen as date format in other applications?

Josh Boxer · August 2022

Hi Steven

You should format the date in Data Studio, before the data it is written to the snapshot or before it is exported:

https://community.experianaperture.io/discussion/549/date-formatting

You can keep only the year, month, day, hour (remove the microseconds, seconds etc.) to the level of granularity that you need in Excel/PowerBI

An alternative is to remove/replace in Excel/PowerQuery the timezone text, i.e. 'Z[UTC]' and excel should be able to interpret it as a date, but will still have the mins and seconds which might cause issues depending on what you are doing with the data.

SpeedySteven · August 2022

This seems to be complicated and unnecessary- why do I need to format the batch timestamp in the first place? Its a time - why does it output as text?

Josh Boxer · September 2022

Hi Steven

The format is the ISO standard datetime format, so you might ask why Microsoft does not recognize it?

https://answers.microsoft.com/en-us/msoffice/forum/all/excel-does-not-recognize-iso-formated-date-time/92cdb33a-6799-4599-92f2-fc3549d6dd8b

This link contains an Excel formula to extract the date, which you might find less complicated.

Thanks for the feedback, we will think if there is an option we could add in future

-Josh

Henry Simms · September 2022

To expand on this a little: Excel doesn't store or use time zones for its datetimes. Dates and times are stored in Excel as numbers and the time zone is assumed to be your current system's time zone. If people in different time zones open the same Excel spreadsheet, the datetime values will be the same for both people, but they may each assume the values represent times in their own time zone.

The Data Studio Batch timestamp is always stored in UTC (in the format "08-Sep-2022 18:25:50.534Z[UTC]") to avoid problems when users in different timezones create batches. In fact, all timestamps stored in Data Studio are converted to, or considered to be in, UTC, and would be exported in the same format.

Batch timestamp format

Answers

Categories