Cannot bind negative time zone minute offset such as `-03:30`.

[kubo]

When a timestamp with negative time zone minute offset such as -03:30 is fetched, dpiTimestamp.tzMinuteOffset is negative. However when the fetched timestamp is set to dpiData_setTimestamp, dpiStmt_execute fails with ORA-01875: time zone minute must be between -59 and 59.

I made a test program.
https://gist.github.com/kubo/3d8cfb06a71454d137e3014475e62674

It printed:

Timestamp value:
  year: 2017
  month: 1
  day: 1
  hour: 0
  minute: 0
  second: 0
  fsecond 0
  tzHourOffset: -3
  tzMinuteOffset: -30

ERROR at line 84
  dpiStmt_execute(stmt, 0, &numQueryColumns)
  ORA-01875: time zone minute must be between -59 and 59

This issue is fixed if ts.tzMinuteOffset in this line is replaced with abs(ts.tzMinuteOffset). However this is strange workaround.

[anthony-tuininga]

OCIDateTimeConstruct() requires a string in the form [+|-][HH:MM] when specifying the time zone. If the time zone minute offset is negative, what does that mean? Currently it tries to parse the string "-03:-30" which is clearly incorrect, even if the error message is a bit odd! I can simply adjust the timezone minute to be the absolute value or I can require the time zone minute offset to be positive. Thoughts? I'll ask internally as well.

[kubo]

If you requires the time zone minute offset to be positive, there is no way to represent "-00:30".

[anthony-tuininga]

True. But other than when the hour offset is zero, does it make any sense for the minute offset to be negative?

[kubo]

But other than when the hour offset is zero, does it make any sense for the minute offset to be negative?

It is easy to calculate total offset from UTC in seconds. (negative in the west hemisphere)

dpiTimestamp *ts = ...;
int offset = ts->tzHourOffset * 3600 + ts->tzMinuteOffset * 60;

If the minute offset is required to be positive except when the hour offset is zero,

dpiTimestamp *ts = ...;
int offset;
if (ts->tzHourOffset >= 0) {
  offset = ts->tzHourOffset * 3600 + ts->tzMinuteOffset * 60;
} else {
  offset = ts->tzHourOffset * 3600 - ts->tzMinuteOffset * 60;
} 

It is, IMO, trade off between conversion from/to total offset from UTC and conversion from/to text representation.

[kubo]

When a timestamp with negative time zone is fetched, tzHourOffset and tzMinuteOffset are zero or negative. This may be by same idea with dpiIntervalDS and dpiIntervalYM. Their members are zero or negative for negative intervals.

[kubo]

I wrote that "It is, IMO, trade off between ..." 5 days ago. But now I think that the minute offset should be zero or negative in the west hemisphere.

Text representation of time zone is '(+|-)hours:minutes'. This consists of three parts: sign, hours and minutes. The sign part is applied after hours and minutes parts are added. For example "-03:30" is calculated as UTC - ('3 hours' + '30 minutes'). However dpiTimestamp doesn't have sign part and the rests are signed integer. In this case, it is natural to calculate "-03:30" as UTC + '-3 hours' + '-30 minutes'. It is strange to me to apply the sign of hours part to minutes part in order to request the minutes part positive.

[anthony-tuininga]

I have added code to correct this issue. When fetched, tzMinuteOffset is negative if tzHourOffset is negative and is positive when tzHourOffset is positive. Having the one positive while the other is negative doesn't make much sense. The sign is negative if either one is negative and positive otherwise. Thanks for pointing this issue out!