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.

[dss010101]

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.

There isnt an issue with the backend really. its doing the right thing. i just need the front end to NOT pass the timezone part or be able to generate a json where time is in EDT/EST instead of UTC.

your solution is the that's the crux of the problem. im using the JsonCOnverter library and it doesnt give me an option not to use ConvertToIso or to specify the timezone i want to use. it just does it. so id have to change code in that libarary, whcih would effectively mean id have to maintain it going forth. not something i want to do.

for now on the back-end im just forcing a conversion to EDT. its a bit of a hack

[Nick-vanGemeren]

You definitely do not need to change JsonConverter. I've given aworkaround, but I think fixing the web service is better. Upgrading to a database which understands international time would be even better.

Another approach is to follow Bloomberg and stop defining the DATE as a point in time, but more a 'market day' or 'overnight run day identifier'. You can then just transfer a 'yyyy-mm-dd' string (using the method of the workaround). The user's local date time doesn't get involved.

[dss010101]

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.

ultimately this does seem to be an odd issue with JsonConverter.ConvertToJson...when used in VBA, some dates are converted as offset -500 and other as -400, i dont now why.

204  2015-10-31T04:00:00.000Z  None   177.35385  16.778591  None  13.211246
205  2015-11-30T05:00:00.000Z  None  177.822482  15.193575  None  13.249804

This causes a different of whether data ends up as EST 12 midnight or EST 11:00PM in the database
The solution you provided means i would have to pre-process the data prior to sending over to using JsonConverter.
Or i would have to change JsonConverter itself.

[houghtonap]

More than likely the reason why some dates converted with -4:00 and some with -5:00 has to do with whether the date fell in EDT (Eastern Day savings Time) or EST (Eastern Standard Time). You might consider consulting Wikipedia on how date time zones work for a better understanding.

[dss010101]

More than likely the reason why some dates converted with -4:00 and some with -5:00 has to do with whether the date fell in EDT (Eastern Day savings Time) or EST (Eastern Standard Time). You might consider consulting Wikipedia on how date time zones work for a better understanding.

and why would excel decide on one or the other from the same laptop in the same region/state/city/time?
the underlying is a date field w/ no time attributes

[Nick-vanGemeren]

Excel/VBA does not know your time zone. The regional settings for your operating system determine the time zone. System calls are provided for conversion of local date-times to UTC or other time zones.
VBA-UTC is a little lazy and uses system calls which only apply the current algorithm for daylight saving time. New York has been using the same algorithm for many years: EST->EDT second Sunday of March, EDT->EST first Sunday of November. So your results for 2015 are correct since they bridge the change on Nov 1, 2015 02:00.
Your web service should also use system calls to convert back to local time, if your database can't accept UTC time.

See my previous posts about getting round this 'point in time' issue.

[dss010101]

Excel/VBA does not know your time zone. The regional settings for your operating system determine the time zone. System calls are provided for conversion of local date-times to UTC or other time zones. VBA-UTC is a little lazy and uses system calls which only apply the current algorithm for daylight saving time. New York has been using the same algorithm for many years: EST->EDT second Sunday of March, EDT->EST first Sunday of November. So your results for 2015 are correct since they bridge the change on Nov 1, 2015 02:00. Your web service should also use system calls to convert back to local time, if your database can't accept UTC time.

See my previous posts about getting round this 'point in time' issue.

The database can handle utc. its timescaledb and is home to over 20mill rows of data from bloomberg, most through nightly python loader.
This data however comes from an excel spreadsheet - either for back filling purposes or custom calculations.
You said something interesting...the data im uploading for testing is month end only data..i wonder if based on the month, EST or EDT is being applied. I bet that's the issue...

[Nick-vanGemeren]

New York changed algorithm in spring 2007. The old one was: EST->EDT first Sunday of April, EDT->EST last Sunday of October. It seems likely that VBA-UTC (under Windows) will convert incorrectly date-times before 2007 and in the weeks near the start of April and November. On Mac a different conversion method ias used, which may function correctly.

[mohawk2]

So after all these digressions, the original proposal of a backward-compatible enhancement to the specific function mentioned in the OP has still not been implemented. Is there a reason for that?