Remove 'Z' in datetime column that is not UTC

Jiacheng Zhang 20 Reputation points
2023-11-20T16:24:50.7866667+00:00

Hi Team,

Good morning! We have an issue which is in an ADX table, we have a column which is not UTC time (for example EST timezone), in the source json file, we remove 'Z' after the datetime value (for exmaple: {'localtime': '2023-10-20T22:22:22.365'} instead of {'localtime':'2023-10-20T22:22:22.365Z'}), but once load to ADX table, the 'Z' will automated added back to the datetime value.

Then I try to use the datetime_utc_to_local() function from this link: https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/query/datetime-utc-to-local-function. Still the converted datetime column with new timezone, still has a 'Z' followed. (I have attached the query result from the tutorial sample)

In this example, even we use datetime_utc_to_local() function transfer utc time to pacific time, but result is still in UTC format(with 'Z' followed), may I know how can we remove the 'Z' ? Thanks!User's image

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
502 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Wilko van de Velde 2,226 Reputation points
    2023-11-20T17:02:42.3466667+00:00

    Hi @Jiacheng Zhang ,

    Maybe the format of showing the date is an issue within the portal. I don't think this will be an issue when calculating with this date. But when you want to show the datetime without the Z, you can use the format_datetime operator, for example:

    print dt=now()
| extend pacific = datetime_utc_to_local(dt,'US/Pacific')
| extend pacific_formatted = format_datetime(pacific,'yyyy-MM-dd HH:mm:ss.FFFF')

    More info:

    https://video2.skills-academy.com/en-us/azure/data-explorer/kusto/query/format-datetimefunction

    Kind regards,

    Wilko

    Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".

    1 person found this answer helpful.