Batch timestamp format

Options
SpeedySteven
SpeedySteven Member
edited December 2023 in General

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?

Tagged:

Answers

  • Josh Boxer
    Josh Boxer Administrator
    Options

    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
    SpeedySteven Member
    Options

    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
    Josh Boxer Administrator
    Options

    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
    Henry Simms Administrator
    Options

    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.

Categories