Date/Time conversion issue

colombeen · August 14, 2018

Hi everyone

I've been looking around for a way to convert this date/time format to a normal, readable format :

2018-08-13T16:39:23+01:00

But for some reason I can't seem to find a solution.

I've been looking on google, the forum, ... but I'm either using the wrong search terms or there aren't any posts on this specific type of date/time format?

Anyone who can help?

This date/time format something that Active Directory returned when querying bitlocker recovery information.

Greetz

Colombeen

---

In powershell you can do this :

PS H:\> [System.DateTimeOffset] "2018-08-13T16:39:23+01:00"


DateTime      : 13/08/2018 16:39:23
UtcDateTime   : 13/08/2018 15:39:23
LocalDateTime : 13/08/2018 17:39:23
Date          : 13/08/2018 0:00:00
Day           : 13
DayOfWeek     : Monday
DayOfYear     : 225
Hour          : 16
Millisecond   : 0
Minute        : 39
Month         : 8
Offset        : 01:00:00
Second        : 23
Ticks         : 636697751630000000
UtcTicks      : 636697715630000000
TimeOfDay     : 16:39:23
Year          : 2018

Edited August 14, 2018 by colombeen

caramen · August 14, 2018

You can use StringBetween

seem like you got an occurrence....

#include <String.au3>
$Ligne = "2018-08-13T16:39:23+01:00"
$Time = _StringBetween ( $Ligne , "T", "+" )

Msgbox(0,"Time","Time = "&$Time[0])

Or checking GMT "+" or "-"

#include <String.au3>


$Ligne = "2018-08-13T16:39:23+01:00"


$Time = _StringBetween ( $Ligne , "T", "+" )


$iRows = UBound($Time, $UBOUND_ROWS)
       If $iRows = 1 Then
          Msgbox(0,"Time","Time = "&$Time[0])
       Else
          $Time = _StringBetween ( $Ligne , "T", "-" )
       EndIf
       
$iRows = UBound($Time, $UBOUND_ROWS)
       If $iRows = 1 Then
          Msgbox(0,"Time","Time = "&$Time[0])
       EndIf

Edited August 14, 2018 by caramen

colombeen · August 14, 2018

10 minutes ago, caramen said:
You can use StringBetween

seem like you got an occurrence....
#include <String.au3>
$Ligne = "2018-08-13T16:39:23+01:00"
$Time = _StringBetween ( $Ligne , "T", "+" )

Msgbox(0,"Time","Time = "&$Time[0])

Not exactly what i'm looking for... I want to convert this to :

LocalDateTime : 13/08/2018 17:39:23

and not just take the time from the string.

caramen · August 14, 2018

15 minutes ago, colombeen said:
Not exactly what i'm looking for... I want to convert this to :
LocalDateTime : 13/08/2018 17:39:23
and not just take the time from the string.

#include <String.au3>
#include <Array.au3>

$Ligne = "2018-08-13T16:39:23+01:00"


$TimeDay = _StringBetween ( $Ligne , "-", "T" )
_ArrayDisplay ($TimeDay)
$TimeMonth = _StringBetween ( $Ligne , "-", "-" )
$TimeYear = _StringBetween ( $Ligne , "", "-" )
$TimeTime = _StringBetween ( $Ligne , "T", "+" )


$iRows = UBound($TimeTime, $UBOUND_ROWS)
$iRows1 = UBound($TimeDay, $UBOUND_ROWS)
$iRows2 = UBound($TimeMonth, $UBOUND_ROWS)
$iRows3 = UBound($TimeYear, $UBOUND_ROWS)



If $iRows = 1 And $iRows1 = 1 And $iRows2 = 1 And $iRows3 = 1 Then
$Time = $TimeDay[0]&"/"&$TimeMonth[0]&"/"&$TimeYear[0]&" "&$TimeTime[0]
MsgBox (0, "Time" , "Time = "&$Time)
EndIf

I am missing something the day is not correctly show but it is what you want i just worked a bit more... ...

Edited August 14, 2018 by caramen

caramen · August 14, 2018

#include <String.au3>
#include <Array.au3>

$Ligne = "2018-08-13T16:39:23+01:00"

                       $TimeDay = _StringBetween ( $Ligne , "01-", "T",$STR_ENDISSTART  )
   $iRows1 = UBound($TimeDay, $UBOUND_ROWS)
    If $iRows1= 0 Then $TimeDay = _StringBetween ( $Ligne , "02-", "T",$STR_ENDISSTART  )
   $iRows1 = UBound($TimeDay, $UBOUND_ROWS)
    If $iRows1= 0 Then $TimeDay = _StringBetween ( $Ligne , "03-", "T",$STR_ENDISSTART  )
   $iRows1 = UBound($TimeDay, $UBOUND_ROWS)
    If $iRows1= 0 Then $TimeDay = _StringBetween ( $Ligne , "04-", "T",$STR_ENDISSTART  )
   $iRows1 = UBound($TimeDay, $UBOUND_ROWS)
    If $iRows1= 0 Then $TimeDay = _StringBetween ( $Ligne , "05-", "T",$STR_ENDISSTART  )
   $iRows1 = UBound($TimeDay, $UBOUND_ROWS)
    If $iRows1= 0 Then $TimeDay = _StringBetween ( $Ligne , "06-", "T",$STR_ENDISSTART  )
   $iRows1 = UBound($TimeDay, $UBOUND_ROWS)
    If $iRows1= 0 Then $TimeDay = _StringBetween ( $Ligne , "07-", "T",$STR_ENDISSTART  )
   $iRows1 = UBound($TimeDay, $UBOUND_ROWS)
    If $iRows1= 0 Then $TimeDay = _StringBetween ( $Ligne , "08-", "T",$STR_ENDISSTART  )
   $iRows1 = UBound($TimeDay, $UBOUND_ROWS)
    If $iRows1= 0 Then $TimeDay = _StringBetween ( $Ligne , "09-", "T",$STR_ENDISSTART  )
   $iRows1 = UBound($TimeDay, $UBOUND_ROWS)
    If $iRows1= 0 Then $TimeDay = _StringBetween ( $Ligne , "10-", "T",$STR_ENDISSTART  )
   $iRows1 = UBound($TimeDay, $UBOUND_ROWS)
    If $iRows1= 0 Then $TimeDay = _StringBetween ( $Ligne , "11-", "T",$STR_ENDISSTART  )
   $iRows1 = UBound($TimeDay, $UBOUND_ROWS)
    If $iRows1= 0 Then $TimeDay = _StringBetween ( $Ligne , "12-", "T",$STR_ENDISSTART  )


$TimeMonth = _StringBetween ( $Ligne , "-", "-" )
$TimeYear = _StringBetween ( $Ligne , "", "-" )
$TimeTime = _StringBetween ( $Ligne , "T", "+" )


$iRows1 = UBound($TimeDay, $UBOUND_ROWS)
$iRows = UBound($TimeTime, $UBOUND_ROWS)
$iRows2 = UBound($TimeMonth, $UBOUND_ROWS)
$iRows3 = UBound($TimeYear, $UBOUND_ROWS)



If $iRows = 1 And $iRows1 = 1 And $iRows2 = 1 And $iRows3 = 1 Then
$Time = $TimeDay[0]&"/"&$TimeMonth[0]&"/"&$TimeYear[0]&" "&$TimeTime[0]
MsgBox (0, "Time" , "Time = "&$Time)
EndIf

Edited August 14, 2018 by caramen

colombeen · August 14, 2018

holy cr*p that's alot of lines for something i expected to see natively in autoit for date/time conversion.

I'll try it out

caramen · August 14, 2018

I am not familiar with String's command there is probably a shorder script that can do that but well my technique work

czardas · August 14, 2018

This is a bit brute force with less lines. I imagine someone might jump in and perform a clever trick.

Local $sTestString = '2018-08-13T16:39:23+01:00 '
Local $sConversion = StringRegExpReplace($sTestString, '(\d+)(\D)(\d+)(\D)(\d+)(\D)([^\+]+)(.+)', '$5/$3/$1 $7')

MsgBox(0, "", $sConversion)

Edited August 14, 2018 by czardas

Jos · August 14, 2018

Anything wrong with the standard incorporated UDF's I wrote over a decade ago, which supports the ansi date/time notation?

#Include<date.au3>
Local $sTestString = '2018-08-13T16:39:23+01:00 '
$sConversion = _dateTimeFormat($sTestString,1)
MsgBox(0, "", $sConversion)
$sConversion = _dateTimeFormat($sTestString,2) & " " & _dateTimeFormat($sTestString,5)
MsgBox(0, "", $sConversion)

Edited August 14, 2018 by Jos

jchd · August 14, 2018

Well, almost :evil:

The timezone is ignored, at least in all the propositions made until now.

The OP didn't tell us clearly which "time" is needed: UTC, local, origin.

Here's how to properly convert an ISO-8601 stamp into what OP calls "readable format" (here I assume UTC aka Zulu time):

Local $sTestString = [ _
    '2018-08-13T16:39:23+01:00 ', _
    '2018-08-13T16:39:23+09:30 ', _
    '2018-08-13T16:39:23-09:00 ', _
    '2018-03-01T01:39:23+02:00 ', _
    '2017-12-31T23:39:23-01:00 ' _
]

For $t In $sTestString
    ConsoleWrite(   $t & @TAB & _
                    _IsoDateTimeToZulu($t) & @TAB & _
    @LF)
Next

Func _IsoDateTimeToZulu($s)
    Local $sDT = StringLeft($s, 19)
    $sDT = StringRegExpReplace($sDT, '(\d{4}).(\d\d).(\d\d).(.{8})', '$1/$2/$3 $4')
    Local $iH = -Int(StringMid($s, 20, 3))
    Local $iM = Int(($iH < 0 ? '-' : '') & StringMid($t, 24, 2))
    Return StringRegExpReplace(_DateAdd('h', $iH, _DateAdd('n', $iM, $sDT)), '(\d{4})(/\d\d/)(\d\d)(.{9})', '$3$2$1$4')
EndFunc

It's trivial to convert to either origin time (just ignore timezone) or local time (add local agjustments) instead.

Jos · August 14, 2018

2 minutes ago, jchd said:

Well, almost

Show-off

Modifications/improvements will always be acceptable.

Edited August 14, 2018 by Jos

jchd · August 14, 2018

What I said: the timezone information is ignored.

If you're an admin of a large corp (or just managing worldwide mail), the timestamps you get may come from everywhere. 2018-08-13T16:39:23+01:00 is 2018-08-13 15:39:23 UTC from a point in timezone +1h (Paris). UTC is the lingua franca of time thanks to its unambiguousness (!?!).

Then from UTC you may need or prefer you own local time and even your own legal time (summer/winter).

Jos · August 14, 2018

Understood and hence my comment that we probably need to look at the standard UDF whether this needs to be supported as well.

Jos

jchd · August 14, 2018

I leave this can of worms to the brave!

Given a specific timezone, conversion from/to UTC to/from current localtime is trivial. What's not is so easy is when you only know the location and have to decide which timezone it's in (timezone boundaries did vary over time).

What's an even worse nightmare is dealing with legal times (wall clock time), present, past or future. A number of libraries have tried to keep track of changes of legal time adjustments in history but even that alone is close to untractable. Of course when you note the versatility of legal rules (DST) over some past and try to devise from that what's coming next you just end up with question marks.

You might say I'm nitpicking but when a function is released it's going to be used in countless contexts and bugs (or just lack of exhaustive precision) left inside may have real nasty consequences. For sure we want to avoid that.

Even now, the ISO 8601 specification allows for quite a large number of formats for several distinct semantics: timestamps, deltatime, starttime+duration, etc. Only supporting all the intricacies it covers (e.g. T24:00 or T23:59:60) isn't a job to take lightly!

Juvigy · August 15, 2018

Jos , why in the documentation for _DateTimeFormat the parameter is stated as: Input date in the format "YYYY/MM/DD[ HH:MM:SS]"

When it clearly works with '2018-08-13T16:39:23+01:00 ' too ?

Jos · August 15, 2018

The Helpfile states:

Quote

Remarks

See _DateTimeSplit() for other possible variations of the input date format.

Where it is stated:

Quote

Parameters

$sDate Any of these formats:
"yyyy/mm/dd[ hh:mm[:ss]]"
"yyyy/mm/dd[Thh:mm[:ss]]"
"yyyy-mm-dd[ hh:mm[:ss]]"
"yyyy-mm-dd[Thh:mm[:ss]]"
"yyyy.mm.dd[ hh:mm[:ss]]"
"yyyy.mm.dd[Thh:mm[:ss]]"

This to avoid having to do this for all date/time functions.

Jos

colombeen · August 16, 2018

On 14-8-2018 at 4:07 PM, Jos said:
Anything wrong with the standard incorporated UDF's I wrote over a decade ago, which supports the ansi date/time notation?
#Include<date.au3>
Local $sTestString = '2018-08-13T16:39:23+01:00 '
$sConversion = _dateTimeFormat($sTestString,1)
MsgBox(0, "", $sConversion)
$sConversion = _dateTimeFormat($sTestString,2) & " " & _dateTimeFormat($sTestString,5)
MsgBox(0, "", $sConversion)

Because it didn't show up in my search results :-)

On 14-8-2018 at 5:13 PM, jchd said:

Well, almost :evil:

The timezone is ignored, at least in all the propositions made until now.

The OP didn't tell us clearly which "time" is needed: UTC, local, origin.

Here's how to properly convert an ISO-8601 stamp into what OP calls "readable format" (here I assume UTC aka Zulu time):

Local $sTestString = [ _
    '2018-08-13T16:39:23+01:00 ', _
    '2018-08-13T16:39:23+09:30 ', _
    '2018-08-13T16:39:23-09:00 ', _
    '2018-03-01T01:39:23+02:00 ', _
    '2017-12-31T23:39:23-01:00 ' _
]

For $t In $sTestString
    ConsoleWrite(   $t & @TAB & _
                    _IsoDateTimeToZulu($t) & @TAB & _
    @LF)
Next

Func _IsoDateTimeToZulu($s)
    Local $sDT = StringLeft($s, 19)
    $sDT = StringRegExpReplace($sDT, '(\d{4}).(\d\d).(\d\d).(.{8})', '$1/$2/$3 $4')
    Local $iH = -Int(StringMid($s, 20, 3))
    Local $iM = Int(($iH < 0 ? '-' : '') & StringMid($t, 24, 2))
    Return StringRegExpReplace(_DateAdd('h', $iH, _DateAdd('n', $iM, $sDT)), '(\d{4})(/\d\d/)(\d\d)(.{9})', '$3$2$1$4')
EndFunc

It's trivial to convert to either origin time (just ignore timezone) or local time (add local agjustments) instead.

This is working perfectly!

On 14-8-2018 at 6:33 PM, jchd said:

If you're an admin of a large corp (or just managing worldwide mail), the timestamps you get may come from everywhere. 2018-08-13T16:39:23+01:00 is 2018-08-13 15:39:23 UTC from a point in timezone +1h (Paris). UTC is the lingua franca of time thanks to its unambiguousness (!?!).

Then from UTC you may need or prefer you own local time and even your own legal time (summer/winter).

So true! We manage over 20000 devices and timestamps + timezones are pretty important.

Edited August 16, 2018 by colombeen

HurleyShanabarger · October 26, 2023

On 8/14/2018 at 4:07 PM, Jos said:
Anything wrong with the standard incorporated UDF's I wrote over a decade ago, which supports the ansi date/time notation?
#Include<date.au3>
Local $sTestString = '2018-08-13T16:39:23+01:00 '
$sConversion = _dateTimeFormat($sTestString,1)
MsgBox(0, "", $sConversion)
$sConversion = _dateTimeFormat($sTestString,2) & " " & _dateTimeFormat($sTestString,5)
MsgBox(0, "", $sConversion)

Looks like that this is not working with the current version of AutoIt, is that possible?

Andreik · October 26, 2023

Not sure if the function changed but the valid input format is YYYY/MM/DD[ HH:MM:SS].

PS: it still works with such data 2018-08-13T16:39:23+01

Edited October 26, 2023 by Andreik

Date/Time conversion issue

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Remarks

Parameters

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members