Strange behavior of STR_TO_UTC for -01:00 offset

SQL++
1

Hi,
I noticed a strange behavior of STR_TO_UTC conversion function when the offset is -01:00.

When I execute command:

SELECT STR_TO_UTC('2024-09-15T08:32:56.123-02:00'), STR_TO_UTC('2024-09-15T08:32:56.123-01:00'), STR_TO_UTC('2024-09-15T08:32:56.123+00:00')

I got this output:

[
  {
    "$1": "2024-09-15T10:32:56.123Z",
    "$2": "2024-09-15T08:32:56.123Z",
    "$3": "2024-09-15T08:32:56.123Z"
  }
]

Offset -02:00 is correctly transformed to time 10:32UTC, however for offset -01:00 time remains the same as for zero offset variant (08:32). Is this intentional and I’m missing something about timezone conversions or this is a bug?

I’m using official Docker image community-7.6.2

Thanks

2

I opened ticket https://jira.issues.couchbase.com/browse/MB-64089

1 Like
3

Please try with the format specifier -

SELECT STR_TO_UTC('2024-09-15T08:32:56.123-02:00','%F')
             ,STR_TO_UTC('2024-09-15T08:32:56.123-01:00','%F')
            ,STR_TO_UTC('2024-09-15T08:32:56.123+00:00','%F')
       ;
{
    "requestID": "57d2efe4-8327-416c-90b7-56568d4fe8e9",
    "signature": {
        "$1": "string",
        "$2": "string",
        "$3": "string"
    },
    "results": [
    {
        "$1": "2024-09-15T10:32:56.123Z",
        "$2": "2024-09-15T09:32:56.123Z",
        "$3": "2024-09-15T08:32:56.123Z"
    }
1 Like
4

Yes, that works. thanks!
However it is strange that if I try explicit format STR_TO_UTC('2024-09-01T08:32:56.123-02:00', 'YYYY-MM-DDThh:mm:ss.sTZD') it yields the same result as without the format.

5

Thanks, '%F' explicit format specifier helped. However it is strange that YYYY-MM-DDThh:mm:ss.sTZD format returns same result as without any format.

6

This actually depends on the machine; the bug is a zone with an offset explicitly of -1 hours is incorrectly interpreted as meaning “use the server time zone”.

The bug @mreiche opened will address this.

1 Like
7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.