Unsupported query: timezone() doesn't support UTC offsets

[timgraham]

The timezone() function (AT TIME ZONE) doesn't seem to accept UTC offsets like PostgreSQL does:

Sample query:

SELECT EXTRACT(hour FROM "db_functions_dtmodel"."start_datetime" AT TIME ZONE 'UTC-05:00') AS "hour_with_delta_pos"
FROM "db_functions_dtmodel"

Django test failure:

ERROR: test_extract_func_with_timezone (db_functions.datetime.test_extract_trunc.DateFunctionWithTimeZoneTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.InternalError: timezone(): unknown time zone UTC-05:00


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/tim/code/django/tests/db_functions/datetime/test_extract_trunc.py", line 1016, in test_extract_func_with_timezone
    utc_model = qs.get()
  File "/home/tim/code/django/django/db/models/query.py", line 411, in get
    num = len(clone)
  File "/home/tim/code/django/django/db/models/query.py", line 258, in __len__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1261, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 57, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1104, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.InternalError: timezone(): unknown time zone UTC-05:00

[rafiss]

I think this might be resolved by cockroachdb/cockroach#41776 which has an open PR out.

[timgraham]

Fixed on cockroachdb master.

[timgraham]

Actually, I still see this error with the latest snapshot of master, v20.1.0-alpha20191216.

[awoods187]

@otan to take a look

[otan]

To double check, it works on master but not in the Alpha? What commands are you running - - the same as the one posted?

[timgraham]

Neither master nor alpha works. I thought it was working previously but I may have made a mistake when testing. Yes, the sample query from this issue description is what fails.

[otan]

okay, PR is up to fix it!

[timgraham]

Hi @otan, it looks like there may be a bug in this fix. For example, I see this test case in your commit:

SELECT '2001-01-01 01:00:00'::timestamptz AT TIME ZONE 'UTC-3:00'
----
2000-12-31 22:00:00 +0000 +0000

however, on PostgreSQL, the same query gives:
2001-01-01 09:00:00.

Similarly, in Django's test suite, CockroachDB is giving incorrect values when AT TIME ZONE is used with a UTC offset. I can give the specific details if you need them.

[otan]

hey tim - yeah I realised this recently and have cockroachdb/cockroach#44099 merging shortly. Sorry about that - I guess I will never master time :P

[otan]

also it does sound like in your case, postgres defaults to your local time zone and cockroach uses UTC, which should explain the 11 hour difference between results (5 for timezone, 3+3 for UTC-3 being flipped the wrong way around)

[timgraham]

I confirmed the test is passing now. Thanks!