ConvertToIso should have from/to timezone capability

[mohawk2]

Current signature:

Public Function ConvertToIso(utc_LocalDate As Date) As String

The behaviour currently treat the parameter as being in the "local" timezone, and without being asked, converts it to UTC and returns the ISO 8601 with "Z" timezone. This is because VBA "dates" are (in Python terminology) "timezone-naive".

Proposed new signature:

Public Function ConvertToIso(utc_LocalDate As Date, Optional fromTZhours As Variant = Empty, Optional toTZhours As Double = 0) As String

This would be backwards-compatible in preserving the old behaviour by default. It would make the passed-in date be "timezone-aware", but defaulting to local.

The local TZ would be detected with an "empty" fromTZhours (using IsEmpty()), and converting the input date into a real UTC one, effectively timezone-aware. The fromTZhours value would be either "empty" (local), or a Double of hours.

It would then be converted to one in the toTZhours, by multiplying that quantity by 60 and applying that as a DateDiff in minutes (which catches offsets with non-integer number of hours, which do exist). The timezone string would be calculated appropriately, with "Z" as a special case for 0.

@timhall Want me to make a PR?

[mohawk2]

I don't see an alternative to making a proper timezone-aware datetime object. I suggest this, which has worked for me. If you want, I will be pleased to make a PR for it (it does utilise TimeZoneConverter from https://stackoverflow.com/questions/3120915/get-timezone-information-in-vba-excel/46052360#46052360 which would need to be incorporated):

Option Explicit

' depends on Iso8601Functions to function
' depends on TimeZoneConverter for withTZ ONLY and tests

' USAGE:
' Dim dta As New DateTimeAware
' With dta
'     .DT = DateTime.Now
'     .offset = +60 ' minutes relative to UTC
' End With
' Debug.Print "asIso", dta.asIso
' Debug.Print "DTA other offset:", dta.withOffset(-240).asIso
' Debug.Print "DTA UTC:", dta.withOffset(0).asIso

' ALSO (using string TZs with TimeZoneConverter):
' Dim dta As New DateTimeAware
' With dta
'     .DT = DateTime.Now
'     .TZ = "GMT Standard Time"
' End With
' Debug.Print "asIso", dta.asIso
' Debug.Print "DTA other tz:", dta.withTZ("Central Standard Time").asIso
' Debug.Print "DTA UTC:", dta.withOffset(0).asIso
' Debug.Print "DTA also UTC:", dta.withTZ("UTC").asIso
' Debug.Print "DTA a week before:", dta.add("d", -7).asIso

' Errors:
' 11010 - tried .asIso with no offset or TZ. This class is for timezone-*aware*!
' 11011 - tried .withOffset with no offset and no TZ given
' 11012 - tried .withTZ with no TZ

Public DT As Date ' timezone-naive native VBA type
Public offset As Variant ' minutes relative to UTC - empty if not set
Public TZ As Variant ' understood by TimeZoneConverter.ConvertTime - empty if not set

' ISO 8601 format
Property Get asIso() As String
    If IsEmpty(Me.offset) And IsEmpty(Me.TZ) Then: Err.Raise 11010, "DateTimeAware.asIso", "no offset and no TZ given, can't calculate ISO 8601"
    asIso = Iso8601Functions.ConvertToIsoNaive(Me.DT)
    If IsEmpty(Me.offset) Then: Me.offset = TimeZoneConverter.GetOffsetAt(Me.DT, Me.TZ)
    asIso = asIso & Iso8601Functions.ConvertOffsetToISO(Me.offset)
End Function

' ISO 8601 format
Property Get dateOnly() As String
    dateOnly = Iso8601Functions.GetIsoDateOnlyNaive(Me.DT)
End Property

Function withOffset(newOffset As Long) As DateTimeAware
    If IsEmpty(Me.offset) And IsEmpty(Me.TZ) Then: Err.Raise 11011, "DateTimeAware.withOffset", "no offset and no TZ given"
    If IsEmpty(Me.offset) Then: Me.offset = TimeZoneConverter.GetOffsetAt(Me.DT, Me.TZ)
    Dim newDta As New DateTimeAware
    With newDta
        .DT = DateAdd("n", newOffset - Me.offset, Me.DT)
        .offset = newOffset
    End With
    Set withOffset = newDta
End Function

' as understood by TimeZoneConverter.ConvertTime
Function withTZ(newTZ As String) As DateTimeAware
    If IsEmpty(Me.TZ) Then: Err.Raise 11012, "DateTimeAware.withTZ", "no TZ given for DateTimeAware object"
    Set withTZ = Me.withOffset(TimeZoneConverter.GetOffsetAt(TimeZoneConverter.ConvertTime(Me.DT, Me.TZ, newTZ), newTZ))
    withTZ.TZ = newTZ
End Function

Function add(interval As String, quantity As Double) As DateTimeAware
    If IsEmpty(Me.TZ) Then
        Set add = Me.withOffset(Me.offset)
        add.DT = DateAdd(interval, quantity, Me.DT)
        Exit Function
    End If
    Dim newDta As New DateTimeAware
    newDta.TZ = Me.TZ
    Dim utcDT As Date: utcDT = TimeZoneConverter.ConvertTime(Me.DT, Me.TZ, "UTC")
    utcDT = DateAdd(interval, quantity, utcDT)
    newDta.DT = TimeZoneConverter.ConvertTime(utcDT, "UTC", newDta.TZ)
    Set add = newDta
End Function

' Dim dta As New DateTimeAware: dta.test_DateTimeAware
Sub test_DateTimeAware()
    Dim hereTZ As String: hereTZ = "GMT Standard Time"
    Dim otherTZ As String: otherTZ = "Eastern Standard Time"
    Dim d1 As Date: d1 = #8/2/2017 2:15:05 AM# ' American, so m/d/y. summertime, crosses date boundary in NY
    Dim d2 As Date: d2 = #2/2/2017 2:15:05 AM# ' non-summertime
    Dim dta1Offset As New DateTimeAware: With dta1Offset
        .DT = d1
        .offset = 60
    End With
    Dim dta2Offset As New DateTimeAware: With dta2Offset
        .DT = d2
        .offset = 0
    End With
    Debug.Assert dta1Offset.asIso = "2017-08-02T02:15:05+01:00"
    Debug.Assert dta1Offset.dateOnly = "2017-08-02"
    Debug.Assert dta1Offset.withOffset(-240).asIso = "2017-08-01T21:15:05-04:00"
    Debug.Assert dta1Offset.withOffset(0).asIso = "2017-08-02T01:15:05Z"
    Debug.Assert dta1Offset.add("m", -6).asIso = "2017-02-02T02:15:05+01:00"
    Debug.Assert dta2Offset.asIso = "2017-02-02T02:15:05Z"
    Debug.Assert dta2Offset.withOffset(-300).asIso = "2017-02-01T21:15:05-05:00"
    Dim dta1TZ As New DateTimeAware: With dta1TZ
        .DT = d1
        .TZ = hereTZ
    End With
    Dim dta2TZ As New DateTimeAware: With dta2TZ
        .DT = d2
        .TZ = hereTZ
    End With
    Debug.Assert dta1TZ.asIso = "2017-08-02T02:15:05+01:00"
    Debug.Assert dta1TZ.withTZ(otherTZ).asIso = "2017-08-01T21:15:05-04:00"
    Debug.Assert dta2TZ.asIso = "2017-02-02T02:15:05Z"
    Debug.Assert dta2TZ.withTZ(otherTZ).asIso = "2017-02-01T21:15:05-05:00"
    Debug.Assert dta1TZ.add("m", -6).asIso = "2017-02-02T01:15:05Z"
    Debug.Print "DTA passed"
End Sub

[mohawk2]

And the Iso8601Functions mentioned, separated out as they are all pure functions:

Option Explicit

' returns "(+/-)HH:MM" or "Z" for UTC
Function ConvertOffsetToISO(ByVal offset As Long) As String
    If offset = 0 Then
        ConvertOffsetToISO = "Z"
        Exit Function
    End If
    If offset > 0 Then
        ConvertOffsetToISO = "+"
    Else
        ConvertOffsetToISO = "-"
    End If
    offset = Math.Abs(offset)
    ConvertOffsetToISO = ConvertOffsetToISO & asTwoDigits(offset \ 60) & ":" & asTwoDigits(offset Mod 60)
End Function

Function GetIsoDateOnlyNaive(when As Date) As String
    GetIsoDateOnlyNaive = VBA.Format$(when, "yyyy-mm-dd")
End Function

Public Function ConvertToIsoNaive(datetime As Date) As String
    ConvertToIsoNaive = VBA.Format$(datetime, "yyyy-mm-ddTHH:mm:ss")
End Function

' must be non-negative
Private Function asTwoDigits(n As Long) As String
    asTwoDigits = ""
    If n < 10 Then
        asTwoDigits = asTwoDigits & "0"
    End If
    asTwoDigits = asTwoDigits & n
End Function

Sub test_Iso8601Functions()
    Dim t As Date: t = #8/23/2017 6:15:05 AM#
    Debug.Print "date only:", GetIsoDateOnlyNaive(t)
    Debug.Print "offsetStr 0", ConvertOffsetToISO(0)
    Debug.Print "offsetStr", ConvertOffsetToISO(30)
    Debug.Print "offsetStr", ConvertOffsetToISO(60)
    Debug.Print "offsetStr", ConvertOffsetToISO(90)
    Debug.Print "offsetStr", ConvertOffsetToISO(-90)
End Sub

[jonadv]

An ISO date is in UTC format which is a global time unit. As described in provided documentation link above the function utc_TzSpecificLocalTimeToSystemTime. As far as I understand an ISO time unit is independent of local times. Thus adding or substracting hours in reference to your local time.

What happens in the function ConvertToJson is that when the json value is of type VBA.vbDate then the datevalue is converted to ISO by the ConvertToIso function, thus adding or substracting hours.

However, when parsing the json value, it will never call the ParseISO method (and correct the time back to your local time unit). This is the real problem (at least for me). I think the writer either forgot (because his local time zone is +0 hours to UTC time) or because testing if a string is actually an ISO date is tricky as it can have multiple formats (see here for example). Above that, another reason for not implementing this yet might be because a json-value might already be in local time format and therefor not need time adjustment.

I use this module to store values temporary. I noticed a time of 'now' (13:06 in afternoon) was stored as 2021-05-14T11:06:51.000Z, and when converted back, the same string was given. I solved it for myself by adding 1 line of code to the module, checking only for 1 format of ISO - the same one that is used to convert a date to JSON (see ConvertToISO method, "yyyy-mm-ddTHH:mm:ss.000Z").

By adding the following line of code in the json_ParseString method, conversion is happing both ways, when converting to and parsing from json.

If json_ParseString Like "####-##-##T##:##:##.###Z" Then json_ParseString = ParseIso(json_ParseString)

So the block of code becomes:

    Case json_Quote
        json_ParseString = json_BufferToString(json_Buffer, json_BufferPosition)
        If json_ParseString Like "####-##-##T##:##:##.###Z" Then json_ParseString = ParseIso(json_ParseString) 
        json_Index = json_Index + 1
        Exit Function
    Case Else

I'm only assuming you encountered the same problem as you are posting this in the VBA-JSON repository, and not in the repository where he got this code from (his UTC-converter).

[dss010101]

im not sure if this is the same issue, but im using this libaray, and if a date of 1/31/1999 is passed to the ConvertToIso functions, it returns:
1999-01-31T05:00:00.000Z.

what i want is to not have that timezone shifted hour, instead i would like
1999-01-31T00:00:00.000Z

anyone know how to solve?

[Nick-vanGemeren]

Your use of 1/31/1999 (M/D/Y) suggests that you are in the US with a local time zone UTC-5. So your VBA date 36191 = local 31 Jan 1999 00:00 is converted to the international standard at UTC+0 = 1999-01-31T05:00:00.000Z. That is the value you should use for communication. An alternative is 1999-01-31T00:00:00.000-05:00; any ISO8601 parsing routine should understand both.

1999-01-31T00:00:00.000Z is a different point in time. If you really want a string in local time, you can convert directly: localISO = Format(localDate,"yyyy-mm-ddThh:mm:ss")

[dss010101]

Your use of 1/31/1999 (M/D/Y) suggests that you are in the US with a local time zone UTC-5. So your VBA date 36191 = local 31 Jan 1999 00:00 is converted to the international standard at UTC+0 = 1999-01-31T05:00:00.000Z. That is the value you should use for communication. An alternative is 1999-01-31T00:00:00.000-05:00; any ISO8601 parsing routine should understand both.

1999-01-31T00:00:00.000Z is a different point in time. If you really want a string in local time, you can convert directly: localISO = Format(localDate,"yyyy-mm-ddThh:mm:ss")

Thanks much. yes i need it in local time w/o the timezone. i need it to match the daily eod data i pull from Bloomberg, which comes in like this '2022-02-01 00:00:00.000" from what i see.

[Nick-vanGemeren]

The string that you show from Bloomberg is not an ISO8601 string (no T) and has no time zone indication (so presumed your local time). Is that what you see in the raw data or has it been massaged by a JSON converter or Bloomberg wrapper …? Is that an actual String value in VBA or a display of some other variable type?

I can’t see a problem here. Maybe post some code and data and describe what unexpected results you get?

'2022-02-01 00:00:00.000" would be a ‘end of local day’ for 31 Jan 2022, not 31 Jan 1999.

[dss010101]

The string that you show from Bloomberg is not an ISO8601 string (no T) and has no time zone indication (so presumed your local time). Is that what you see in the raw data or has it been massaged by a JSON converter or Bloomberg wrapper …? Is that an actual String value in VBA or a display of some other variable type?

I can’t see a problem here. Maybe post some code and data and describe what unexpected results you get?

'2022-02-01 00:00:00.000" would be a ‘end of local day’ for 31 Jan 2022, not 31 Jan 1999.

The data in the json receieved from the bbg request looks like this:

{
       "DL_REQUEST_ID": "xxxxxxxxxxxxxxxxxx",
       "DL_REQUEST_NAME": "RequestPayload",
       "DL_SNAPSHOT_START_TIME": "2024-04-09T15:20:38",
       "DL_SNAPSHOT_TZ": "EDT",
       "IDENTIFIER": "GDX  US Equity",
       "RC": 0,
       "DATE": "2006-05-25",
       "PX_LAST": 38.32,
       "PX_VOLUME": 367000,
       "PX_OPEN": 37.18,
       "PX_LOW": 36.98,
       "PX_HIGH": 38.32,
       "TOT_RETURN_INDEX_GROSS_DVDS": 38.32
   },

So reading and loading to a pandas dataframe it ends up without a timezone. Im fine with that - as i can assume the source data is EST locale.

In excel, i allow override of this data. if the date in the excel column is "1/31/1999", after conversion using ConvertToIso, it ends up with a timezone part like this 1999-01-31T05:00:00.000Z

If i upload that to the database like this, i would end up with functionally with two duplicate 'DATE' since its effectively two datetimestamps. so thats the problem im trying to solve. I would prefer solving it by massaging or coercing the data from the client excel, and leaving the backend as is to support flexibility of storing dates + timestamped data.

[Nick-vanGemeren]

We'll forgive you the backtick problems. Use the Preview tab to check formatting before posting.

I am not familiar with the Bloomberg interface or 'pandas dataframe'.

There are two datetimes in that JSON. I think you are referring by 'it' to the DATE value. The DL_SNAPSHOT_START_TIME is probably not relevant. I don't immediately see where you get two datetimes going to the database.

The DATE value is just a simple date string, not date-time as you wrote.

If you are analysing the JSON with JsonConverter's ParseJson, its (VBA-UTC's) ParseISO function will return the DATE value as a local Date (sidestepping a bug for strings with time but without time zone).

So the Excel cell should have a clean Date value (without fraction). It's a bit weird to allow users to modify historical data, but ... OK as long as they only input a valid date.

The cell is then exported to the database as a text string. The database can probably accept several formats which it will convert to its internal format. A tolerant database could accept any of these as equivalents (Withthe cell Range):

• "05/25/2006" = .Text ' local date format
• "2006-05-25" = Format(.Value, "yyyy-mm-dd") ' ISO date (local)
• "2006-05-25T00:00:00" = Format(.Value, "yyyy-mm-ddThh:mm:ss") ' ISO datetime (local)
• "2006-05-25T05:00:00Z" = ConvertToIso(.Value) ' ISO datetime (global)

If the database does not handle the field as a datetime, you should get that corrected.

i would end up with functionally with two duplicate 'DATE' since its effectively two datetimestamps

Please explain this and show the practical results.

[dss010101]

We'll forgive you the backtick problems. Use the Preview tab to check formatting before posting.

I am not familiar with the Bloomberg interface or 'pandas dataframe'.

There are two datetimes in that JSON. I think you are referring by 'it' to the DATE value. The DL_SNAPSHOT_START_TIME is probably not relevant. I don't immediately see where you get two datetimes going to the database.

The DATE value is just a simple date string, not date-time as you wrote.

If you are analysing the JSON with JsonConverter's ParseJson, its (VBA-UTC's) ParseISO function will return the DATE value as a local Date (sidestepping a bug for strings with time but without time zone).

So the Excel cell should have a clean Date value (without fraction). It's a bit weird to allow users to modify historical data, but ... OK as long as they only input a valid date.

The cell is then exported to the database as a text string. The database can probably accept several formats which it will convert to its internal format. A tolerant database could accept any of these as equivalents (Withthe cell Range):

• "05/25/2006" = .Text ' local date format
• "2006-05-25" = Format(.Value, "yyyy-mm-dd") ' ISO date (local)
• "2006-05-25T00:00:00" = Format(.Value, "yyyy-mm-ddThh:mm:ss") ' ISO datetime (local)
• "2006-05-25T05:00:00Z" = ConvertToIso(.Value) ' ISO datetime (global)

If the database does not handle the field as a datetime, you should get that corrected.

i would end up with functionally with two duplicate 'DATE' since its effectively two datetimestamps

Please explain this and show the practical results.

Ok let me step back and explain this more clearly as i think there is some confusion here.
There are two parts to the process. A nightly python process that uses the bloomberg api to fetch data for some securities and upsert(insert or update) those into a timeseries table in the database.

The 2nd part is an excel spreadsheet that allows a user to specify and update their own data for a security. this data is read from the excel spreadsheet, converted to json using the JsonConverter library and posted to a backend webservice to upsert (insert or update) into the database's timeseries table, with key being security and date.
Effectively, this allows a user to overwrite the data downloaded if they like.

an example of data uploaded from bloomberg would insert a date that when queried in the database, would show like this this: 2022-01-31 00:00:00.000

on the other hand, data posted from the excel spreadsheet, as mentioned is converted to json using the JsonConverter library and when received by the backend python webservice looks like this in the json: 2006-01-31T05:00:00Z and when inserted and queried in the database, looks like this: 2022-01-31 05:00:00.000

So now i have two rows for ticker XYZ with the same date, but different time:

      identifier        |date                   |px_last|
      --------------+--------------------------+--------+
      XYZ		|2022-01-31 00:00:00.000|  30.23|  <--overnight process
      XYZ		|2022-01-31 05:00:00.000|  32.25|  <--uploaded from excel

So now when i ask for data for 2022-01-31, there are two records instead of 1. IN the above example, i can solve if i can make sure the data posted from excel does not have the timezone part added or set it such that it's '00:00:00.000'. if that is the case, the upsert function will update and not insert, since a record with the same ticker and date value exists already.

[Nick-vanGemeren]

From that explanation, it's clear that the time zone info is being lost by or after the backend web service. Either the service is badly interpreting the JSON (and not even raising an error on a string it doesn't understand) or there is a problem in the update process. Fixing that will be better than trying to work around the bug(s).

You should also check the application design will still give sensible results if some users move to Chicago (CDT) or elsewhere. This could avert a crisis later.

[Nick-vanGemeren]

If you really can't wait for the web service to be fixed, I've found a temporary workaround:
When you are building the Dictionary that you will convert to JSON, instead of copying the Date value, format it into the local ISO datetime string. No ConvertToIso call will be needed.