[
https://issues.apache.org/jira/browse/CASSANDRA-19566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17838154#comment-17838154
]
Stefan Miklosovic edited comment on CASSANDRA-19566 at 4/17/24 1:46 PM:
------------------------------------------------------------------------
It does not. I wonder what the reason might be.
{code:java}
cqlsh:test> select tounixtimestamp(ts), ts, tojson(ts) from tbl;
system.tounixtimestamp(ts) | ts |
system.tojson(ts)
----------------------------+---------------------------------+----------------------------
-13767019200000 | 1533-09-28 12:00:00.000000+0000 | "1533-09-18
12:00:00.000Z"
13767019200000 | 2406-04-05 12:00:00.000000+0000 | "2406-04-05
12:00:00.000Z"
{code}
Going into negative values before Jan 1 1970 works
{code:java}
cqlsh:test> select tounixtimestamp(ts), ts, tojson(ts) from tbl where id=2;
system.tounixtimestamp(ts) | ts |
system.tojson(ts)
----------------------------+---------------------------------+----------------------------
-1000 | 1969-12-31 23:59:59.000000+0000 | "1969-12-31
23:59:59.000Z"
{code}
but, obviously, for some big negative number is starts to break.
edit:
so, it starts to happen between
{code:java}
cqlsh:test> select id, tounixtimestamp(ts), ts, tojson(ts) from tbl where id in
(13,16, 15, 17, 18);
id | system.tounixtimestamp(ts) | ts |
system.tojson(ts)
----+----------------------------+---------------------------------+----------------------------
13 | -12200000000000 | 1583-05-26 07:06:40.000000+0000 |
"1583-05-26 07:06:40.000Z"
15 | -12225000000000 | 1582-08-09 22:40:00.000000+0000 |
"1582-07-30 22:40:00.000Z"
16 | -12215000000000 | 1582-12-03 16:26:40.000000+0000 |
"1582-12-03 16:26:40.000Z"
17 | -12220000000000 | 1582-10-06 19:33:20.000000+0000 |
"1582-09-26 19:33:20.000Z"
18 | -12218000000000 | 1582-10-29 23:06:40.000000+0000 |
"1582-10-29 23:06:40.000Z"
{code}
29th October 1582 and 26th September 1582.
In 1582, there was Gregorian calendar introduced:
Date Adjustment: To realign the date of the vernal equinox to March 21st (which
was the date of the equinox at the time of the First Council of Nicaea in AD
325 and critical for determining the date of Easter), Thursday, October 4,
1582, was followed by Friday, October 15, 1582. This correction effectively
"skipped" 10 days in the calendar.
There is indeed drift of 10 days.
So what happens here is that when we go to negative values, whatever we use for
date parsing does not correctly parse negative values when Gregorian calendar
was introduced.
was (Author: smiklosovic):
It does not. I wonder what the reason might be.
{code}
cqlsh:test> select tounixtimestamp(ts), ts, tojson(ts) from tbl;
system.tounixtimestamp(ts) | ts |
system.tojson(ts)
----------------------------+---------------------------------+----------------------------
-13767019200000 | 1533-09-28 12:00:00.000000+0000 | "1533-09-18
12:00:00.000Z"
13767019200000 | 2406-04-05 12:00:00.000000+0000 | "2406-04-05
12:00:00.000Z"
{code}
I wonder what sense it makes to enable timestamp to be a negative value.
Going into negative values before Jan 1 1970 works
{code}
cqlsh:test> select tounixtimestamp(ts), ts, tojson(ts) from tbl where id=2;
system.tounixtimestamp(ts) | ts |
system.tojson(ts)
----------------------------+---------------------------------+----------------------------
-1000 | 1969-12-31 23:59:59.000000+0000 | "1969-12-31
23:59:59.000Z"
{code}
but, obviously, for some big negative number is starts to break.
edit:
so, it starts to happen between
{code}
cqlsh:test> select id, tounixtimestamp(ts), ts, tojson(ts) from tbl where id in
(13,16, 15, 17, 18);
id | system.tounixtimestamp(ts) | ts |
system.tojson(ts)
----+----------------------------+---------------------------------+----------------------------
13 | -12200000000000 | 1583-05-26 07:06:40.000000+0000 |
"1583-05-26 07:06:40.000Z"
15 | -12225000000000 | 1582-08-09 22:40:00.000000+0000 |
"1582-07-30 22:40:00.000Z"
16 | -12215000000000 | 1582-12-03 16:26:40.000000+0000 |
"1582-12-03 16:26:40.000Z"
17 | -12220000000000 | 1582-10-06 19:33:20.000000+0000 |
"1582-09-26 19:33:20.000Z"
18 | -12218000000000 | 1582-10-29 23:06:40.000000+0000 |
"1582-10-29 23:06:40.000Z"
{code}
29th October 1582 and 26th September 1582.
In 1582, there was Gregorian calendar introduced:
Date Adjustment: To realign the date of the vernal equinox to March 21st (which
was the date of the equinox at the time of the First Council of Nicaea in AD
325 and critical for determining the date of Easter), Thursday, October 4,
1582, was followed by Friday, October 15, 1582. This correction effectively
"skipped" 10 days in the calendar.
There is indeed drift of 10 days.
So what happens here is that when we go to negative values, whatever we use for
date parsing does not correctly parse negative values when Gregorian calendar
was introduced.
> JSON encoded timestamp value does not always match non-JSON encoded value
> -------------------------------------------------------------------------
>
> Key: CASSANDRA-19566
> URL: https://issues.apache.org/jira/browse/CASSANDRA-19566
> Project: Cassandra
> Issue Type: Bug
> Components: Legacy/Core, Legacy/CQL
> Reporter: Bowen Song
> Assignee: Stefan Miklosovic
> Priority: Normal
> Fix For: 5.0.x, 5.x
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Description:
> "SELECT JSON ..." and "toJson(...)" on Cassandra 4.1.4 produces different
> date than "SELECT ..." for some timestamp type values.
>
> Steps to reproduce:
> {code:java}
> $ sudo docker pull cassandra:4.1.4
> $ sudo docker create --name cass cassandra:4.1.4
> $ sudo docker start cass
> $ # wait for the Cassandra instance becomes ready
> $ sudo docker exec -ti cass cqlsh
> Connected to Test Cluster at 127.0.0.1:9042
> [cqlsh 6.1.0 | Cassandra 4.1.4 | CQL spec 3.4.6 | Native protocol v5]
> Use HELP for help.
> cqlsh> create keyspace test WITH replication = {'class': 'SimpleStrategy',
> 'replication_factor': 1};
> cqlsh> use test;
> cqlsh:test> create table tbl (id int, ts timestamp, primary key (id));
> cqlsh:test> insert into tbl (id, ts) values (1, -13767019200000);
> cqlsh:test> select tounixtimestamp(ts), ts, tojson(ts) from tbl where id=1;
> system.tounixtimestamp(ts) | ts |
> system.tojson(ts)
> ----------------------------+---------------------------------+----------------------------
> -13767019200000 | 1533-09-28 12:00:00.000000+0000 | "1533-09-18
> 12:00:00.000Z"
> (1 rows)
> cqlsh:test> select json * from tbl where id=1;
> [json]
> ---------------------------------------------
> {"id": 1, "ts": "1533-09-18 12:00:00.000Z"}
> (1 rows)
> {code}
>
> Expected behaviour:
> The "select ts", "select tojson(ts)" and "select json *" should all produce
> the same date.
>
> Actual behaviour:
> The "select ts" produced the "1533-09-28" date but the "select tojson(ts)"
> and "select json *" produced the "1533-09-18" date.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
