Converting @Timestamp into DateTime

[nzscout]

How can I convert @timestamp property in “SELECT” query in human readable DateTime format for the current Time Zone like it’s in event viewer?
@timestamp gives number of ticks. ToIsoString(@timestamp) converts it into UTC time zone. Is there anything like ToString(@timestamp)?

[KodrAus]

Hi @IgorRider 👋

This isn't something that's possible currently unfortunately. We did just recently add OffsetIn support that lets you supply a specific timezone, but as far as I'm aware we don't have a ToIsoString variant that accepts an offset yet. I think this is a good suggestion though!

[nblumhardt]

Seems like an annoying paper cut..... @IgorRider, would ISO-8601, but in a specified time zone, cover your current needs? I.e. if we implement ToIsoString(x, offset)?

[nzscout]

I can use offset with ToIsoString by using the code below to get the date and time in the local format 2022-03-07 02:40:16
I was just hoping there is something similar to DateTime.ToString(format)
select @Arrived as ID, Substring(ToIsoString(@Timestamp + OffsetIn('Pacific/Auckland', @Timestamp)), 0, LastIndexOf(ToIsoString(TimeOfDay(@Timestamp, DatePart(OffsetIn('Pacific/Auckland', @Timestamp), 'hour', 0h))), 'T')) as Date, Substring(ToIsoString(TimeOfDay(@Timestamp, DatePart(OffsetIn('Pacific/Auckland', @Timestamp), 'hour', 0h))), IndexOf(ToIsoString(TimeOfDay(@Timestamp, DatePart(OffsetIn('Pacific/Auckland', @Timestamp), 'hour', 0h))), 'T') + 1, LastIndexOf(ToIsoString(TimeOfDay(@Timestamp, DatePart(OffsetIn('Pacific/Auckland', @Timestamp), 'hour', 0h))), '.') - IndexOf(ToIsoString(TimeOfDay(@Timestamp, DatePart(OffsetIn('Pacific/Auckland', @Timestamp), 'hour', 0h))), 'T') - 1) as Time

[nblumhardt]

Thanks for your reply. Generic formatting would be harder, at this point in time, but it looks like your example would be served reasonably well (or at least simplified) by ToIsoString(x, offset). We'll look at adding that as a stepping stone, in a near future release.

[nzscout]

Thank you

[nblumhardt]

This is now implemented for the upcoming 2023.1 release.

[dengyakui]

Hi, @nblumhardt, @KodrAus :
I had tried using the offset argument in ToISOString(@Timestamp, <offset ticks>), but the unit (ticks) was so tiny, very hard to pick.
For example. If I use +8 hour offset,
the ticks should be: 8×3600×10000000=288000000000 Can we make the parameter more user friendly to use?
like this: ToISOString(@Timestamp, '+8h'), ToISOString(@Timestamp, '-5h') will be easier for the user.

[liammclennan]

Hi @dengyakui

You are very close. See https://docs.datalust.co/docs/working-with-dates-and-times#duration-literals

Your example would be ToISOString(@Timestamp, 8h)

[dengyakui]

Thanks, @liammclennan . I got it, Cool!

[jrdata]

Hi @dengyakui

You are very close. See https://docs.datalust.co/docs/working-with-dates-and-times#duration-literals

Your example would be ToISOString(@Timestamp, 8h)

This is not clearly documented in the documentation:
https://docs.datalust.co/docs/scalar-functions#toisostringdatetime-offset

The documentation on the provided link could be improved with an example of fx "ToIsoString(@timestamp, 1h)".

It should also be mentioned on this page:
https://docs.datalust.co/docs/working-with-dates-and-times

[liammclennan]

Thank you for the suggestion. I will add some more examples to the docs.

[dazinator]

ToIsoString isn't mentioned on that page luckily i found this.