0

I loaded some datetime texts from a log file to SQL Server. The datetimes look like 2019-03-20T12:00:02.630+11.00 or another example 2019-03-21T12:50:02.640+11.00 etc.

I am based in Melbourne, Australia. First I am not sure whether this datetimes are local data and time of Australia? All the logs, are collected are based in Australia.

Second, can you explain me the format. For example, what is that T means in the middle? What is that +11.00 means at the end? Is it in UTC format, suggesting we are 11+ hours ahead of UTC? If the dates are in UTC, do I change it to local datetime in order for us to better related to the data?

Thanks

Abra
  • 19,142
  • 7
  • 29
  • 41
PS_A_1
  • 3
  • 1

1 Answers1

0

This is ISO 8601 format, a standard date/time format.

2019-03-20T12:00:02.630+11.00

YYYY-MM-DDThh:mm:ss.sss±hh:mm

That is 12:00:02 and 630 milliseconds on March 3rd, 2019. The time zone is 11 hours ahead of UTC.

The T is part of the format, it separates the date portion from the time portion.

First I am not sure whether this datetimes are local data and time of Australia?

Yes, they are local. On March 20th, Melbourne was using Australian Eastern Daylight Time which is 11 hours ahead of UTC. To convert to UTC you'd subtract 11 hours.

Now Melbourne is out of daylight savings time, your timestamps are probably +10.00.

Note that, in general, it's confusing to store timestamps in the local time zone. It's usually better to store time in UTC and convert to the local time zone.

Schwern
  • 153,029
  • 25
  • 195
  • 336
  • Thanks both! Really helpful. So that means, if the time reflects my local time of Melbourne, I do not need to change it to anything. That is, I can safely say an event took place on March 3rd, 2019 at 12:00:02 Melbourne time, right? Because if I follow the advise from https://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement it simply subtract an hour from each record. Should I be using any conversion then? – PS_A_1 Apr 16 '19 at 01:41
  • The timestamp says what a clock on the wall would say in Melbourne on March 3rd at 12:00:02. If that's all you want, you don't need a conversion. However there's been a daylight savings time shift since then, your local time is now +10 and your server might be storing in +10. This can lead to subtle problems where times appear to be off by an hour. Fortunately if you're using a good time library to parse the complete timestamp, including the time zone, it will internally convert it to UTC. – Schwern Apr 16 '19 at 16:09
  • Thanks, yes, all I want to say an event took place in Melbourne on March 3rd at 12:00:02. Thus, removed the conversion. – PS_A_1 Apr 16 '19 at 21:24