So while I'm getting up to speed here, some general tips I picked up from going though slightly similar testing with timestamptz a while back:

First off, I'd avoid any client rendering differences by using psql to query both DBs -- if the DB is acting the same, we can think about the clients later.
For testing the actual DB behavior, I'd suggest making a combination of time, timetz, timestamp and timestamptz columns and then writing to them with and without offsets specified, with and without a session offset, then read them with and without a session offset, in both Cockroach and Postgres.
Finally there's casts and function overloads, but the same strategy applies there -- make a table of selects of all the casts, then run it with/without, etc against both DBs.

Examples:

root=# select version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.2 on x86_64-apple-darwin16.4.0, compiled by Apple LLVM version 8.0.0 (clang-800.0.42.1), 64-bit
(1 row)
root=# set timezone = 0;
SET
root=# create table foo (name varchar, t time, tz timetz, ts timestamp, tsz timestamptz);
CREATE TABLE
root=# insert into foo values ('no-offsets-no-session', '11:00:00', '11:00:00', '2018-04-30 23:00:00', '2018-04-29 23:00:00');
INSERT 0 1
root=# insert into foo values ('offsets-no-session', '11:00:00 -0500', '11:00:00 -0500', '2018-04-30 23:00:00 -0500', '2018-04-29 23:00:00 -0500');
INSERT 0 1
root=# set timezone = -3;
SET
root=# insert into foo values ('no-offsets-session-3', '11:00:00', '11:00:00', '2018-04-30 23:00:00', '2018-04-29 23:00:00');
INSERT 0 1
root=# insert into foo values ('offsets-session-3', '11:00:00 -0500', '11:00:00 -0500', '2018-04-30 23:00:00 -0500', '2018-04-29 23:00:00 -0500');
INSERT 0 1
root=# set timezone = 0;
SET
root=# select * from foo;
         name          |    t     |     tz      |         ts          |          tsz
-----------------------+----------+-------------+---------------------+------------------------
 no-offsets-no-session | 11:00:00 | 11:00:00+00 | 2018-04-30 23:00:00 | 2018-04-29 23:00:00+00
 offsets-no-session    | 11:00:00 | 11:00:00-05 | 2018-04-30 23:00:00 | 2018-04-30 04:00:00+00
 no-offsets-session-3  | 11:00:00 | 11:00:00-03 | 2018-04-30 23:00:00 | 2018-04-30 02:00:00+00
 offsets-session-3     | 11:00:00 | 11:00:00-05 | 2018-04-30 23:00:00 | 2018-04-30 04:00:00+00
(4 rows)

root=# set timezone = -2;
SET
root=# select * from foo;
         name          |    t     |     tz      |         ts          |          tsz
-----------------------+----------+-------------+---------------------+------------------------
 no-offsets-no-session | 11:00:00 | 11:00:00+00 | 2018-04-30 23:00:00 | 2018-04-29 21:00:00-02
 offsets-no-session    | 11:00:00 | 11:00:00-05 | 2018-04-30 23:00:00 | 2018-04-30 02:00:00-02
 no-offsets-session-3  | 11:00:00 | 11:00:00-03 | 2018-04-30 23:00:00 | 2018-04-30 00:00:00-02
 offsets-session-3     | 11:00:00 | 11:00:00-05 | 2018-04-30 23:00:00 | 2018-04-30 02:00:00-02
(4 rows)

And in cockroach:

root=# select version();
                                                         version
-------------------------------------------------------------------------------------------------------------------------
 CockroachDB CCL v2.1-alpha.20180507-379-ge88994b6f-dirty (x86_64-apple-darwin17.5.0, built 2018/05/17 18:39:27, go1.10)
(1 row)
root=# set timezone = 0;
SET
root=# create table foo (name varchar, t time, tz timetz, ts timestamp, tsz timestamptz);
CREATE TABLE
root=# insert into foo values ('no-offsets-no-session', '11:00:00', '11:00:00', '2018-04-30 23:00:00', '2018-04-29 23:00:00');
INSERT 0 1
root=# insert into foo values ('offsets-no-session', '11:00:00 -0500', '11:00:00 -0500', '2018-04-30 23:00:00 -0500', '2018-04-29 23:00:00 -0500');
INSERT 0 1
root=# set timezone = -3;
SET
root=# insert into foo values ('no-offsets-session-3', '11:00:00', '11:00:00', '2018-04-30 23:00:00', '2018-04-29 23:00:00');
INSERT 0 1
root=# insert into foo values ('offsets-session-3', '11:00:00 -0500', '11:00:00 -0500', '2018-04-30 23:00:00 -0500', '2018-04-29 23:00:00 -0500');
INSERT 0 1
root=# set timezone = 0;
SET
root=# select * from foo;
         name          |    t     |       tz       |         ts          |            tsz
-----------------------+----------+----------------+---------------------+---------------------------
 no-offsets-no-session | 11:00:00 | 11:00:00+00:00 | 2018-04-30 23:00:00 | 2018-04-29 23:00:00+00:00
 offsets-no-session    | 11:00:00 | 16:00:00+00:00 | 2018-05-01 04:00:00 | 2018-04-30 04:00:00+00:00
 no-offsets-session-3  | 11:00:00 | 14:00:00+00:00 | 2018-04-30 23:00:00 | 2018-04-30 02:00:00+00:00
 offsets-session-3     | 11:00:00 | 16:00:00+00:00 | 2018-05-01 04:00:00 | 2018-04-30 04:00:00+00:00
(4 rows)

root=# set timezone = -2;
SET
root=# select * from foo;
         name          |    t     |       tz       |         ts          |            tsz
-----------------------+----------+----------------+---------------------+---------------------------
 no-offsets-no-session | 11:00:00 | 09:00:00-02:00 | 2018-04-30 23:00:00 | 2018-04-29 21:00:00-02:00
 offsets-no-session    | 11:00:00 | 14:00:00-02:00 | 2018-05-01 04:00:00 | 2018-04-30 02:00:00-02:00
 no-offsets-session-3  | 11:00:00 | 12:00:00-02:00 | 2018-04-30 23:00:00 | 2018-04-30 00:00:00-02:00
 offsets-session-3     | 11:00:00 | 14:00:00-02:00 | 2018-05-01 04:00:00 | 2018-04-30 02:00:00-02:00
(4 rows)

Looking at that postgres behavior, yes, it looks like it is indeed storing the offset. Thats not how timestamptz vs timestamp works, so it's a bit surprising. If you look at something like Vertica, they don't store it as far as i can tell.

I don't think we want to store it, but obviously we won't match postgres' behavior if we don't. I'm not sure what we actually need for ORM compat.

qa: timetz #25224

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions