You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Ralf Steppacher <ra...@gmail.com> on 2016/02/08 11:09:15 UTC
SELECT JSON timestamp lacks timezone information
Hello all,
When I select a timestamp as JSON from Cassandra, the string representation lacks the timezone information, both via CQLSH and the Java Driver:
cqlsh:events> select toJson(created_at) AS created_at from event_by_patient_timestamp ;
created_at
---------------------------
"2016-01-04 16:05:47.123"
(1 rows)
vs.
cqlsh:events> select created_at FROM event_by_user_timestamp ;
created_at
--------------------------
2016-01-04 15:05:47+0000
(1 rows)
cqlsh:events>
To make things even more complicated the JSON timestamp is not returned in UTC. Is there a way to either tell the driver/C* to return the JSON date in UTC or add the timezone information (much preferred) to the text representation of the timestamp?
Thanks!
Ralf
Re: SELECT JSON timestamp lacks timezone information
Posted by Stefania Alborghetti <st...@datastax.com>.
It's cqlsh that converts timestamps to UTC and adds the timezone but for
JSON it can't do that because the conversion to JSON is done by Cassandra.
I've filed https://issues.apache.org/jira/browse/CASSANDRA-11137 to discuss
further.
On Mon, Feb 8, 2016 at 7:53 PM, Alexandre Dutra <
alexandre.dutra@datastax.com> wrote:
> Sorry,
>
> I mistakenly thought that we were on the Java driver mailing list, my
> apologies. I also think you should definitely file a Jira ticket and ask
> JSON timestamps generated server-side to be 1) formatted with a format that
> mentions the timezone and 2) formatted preferably with UTC, not the JVM
> default timezone.
>
> Alexandre
>
> On Mon, Feb 8, 2016 at 12:23 PM Ralf Steppacher <ra...@gmail.com>
> wrote:
>
>> Hi Alexandre.
>>
>> I wrote to ‘user@cassandra.apache.org’.
>>
>> Re the actual problem: I am aware of the fact that C* does not store
>> (need not store) the timezone as it is persisted as a Unix epoche
>> timestamp. Not delivering a timezone in the JSON text representation would
>> be OKish if the text representation would be guaranteed to be in UTC. But
>> it is not. It is in some timezone determined by the locale of the server
>> side or that of the client VM. That way it is a pain in two ways as
>>
>> a) I have to add the timezone in a post-processing step to all timestamps
>> in my JSON responses and
>> b) I also have to do some guesswork at what the actual timezone might be
>>
>> If there is no way to control the formatting of JSON timestamps and to
>> add the time zone information, then IMHO that is bug. Is it not? Or am I
>> missing something here?
>>
>>
>> Thanks!
>> Ralf
>>
>>
>> On 08.02.2016, at 12:06, Alexandre Dutra <al...@datastax.com>
>> wrote:
>>
>> Hello Ralf,
>>
>> First of all, Cassandra stores timestamps without timezone information,
>> so it's not possible to retrieve the original timezone used when inserting
>> the value.
>>
>> CQLSH uses the python driver behind the scenes, and my guess is that the
>> timestamp formatting is being done driver-side – hence the timezone – while
>> when you call toJson(), the formatting has to be done server-side.
>>
>> That said, it does seem that Cassandra is using a format without timezone
>> when converting timestamps to JSON format:
>>
>> https://github.com/apache/cassandra/blob/cassandra-3.0/src/java/org/apache/cassandra/serializers/TimestampSerializer.java#L52
>>
>> I agree with you that a date format that would include the timezone would
>> be preferable here, but that is a question you should ask in the Cassandra
>> Users mailing list instead.
>>
>> Hope that helps,
>>
>> Alexandre
>>
>>
>>
>>
>> On Mon, Feb 8, 2016 at 11:09 AM Ralf Steppacher <ra...@gmail.com>
>> wrote:
>>
>>> Hello all,
>>>
>>> When I select a timestamp as JSON from Cassandra, the string
>>> representation lacks the timezone information, both via CQLSH and the Java
>>> Driver:
>>>
>>> cqlsh:events> select toJson(created_at) AS created_at from
>>> event_by_patient_timestamp ;
>>>
>>> created_at
>>> ---------------------------
>>> "2016-01-04 16:05:47.123"
>>>
>>> (1 rows)
>>>
>>> vs.
>>>
>>> cqlsh:events> select created_at FROM event_by_user_timestamp ;
>>>
>>> created_at
>>> --------------------------
>>> 2016-01-04 15:05:47+0000
>>>
>>> (1 rows)
>>> cqlsh:events>
>>>
>>> To make things even more complicated the JSON timestamp is not returned
>>> in UTC. Is there a way to either tell the driver/C* to return the JSON date
>>> in UTC or add the timezone information (much preferred) to the text
>>> representation of the timestamp?
>>>
>>>
>>> Thanks!
>>> Ralf
>>
>> --
>> Alexandre Dutra
>> Driver & Tools Engineer @ DataStax
>>
>>
>> --
> Alexandre Dutra
> Driver & Tools Engineer @ DataStax
>
--
[image: datastax_logo.png] <http://www.datastax.com/>
Stefania Alborghetti
Apache Cassandra Software Engineer
|+852 6114 9265| stefania.alborghetti@datastax.com
Re: SELECT JSON timestamp lacks timezone information
Posted by Alexandre Dutra <al...@datastax.com>.
Sorry,
I mistakenly thought that we were on the Java driver mailing list, my
apologies. I also think you should definitely file a Jira ticket and ask
JSON timestamps generated server-side to be 1) formatted with a format that
mentions the timezone and 2) formatted preferably with UTC, not the JVM
default timezone.
Alexandre
On Mon, Feb 8, 2016 at 12:23 PM Ralf Steppacher <ra...@gmail.com>
wrote:
> Hi Alexandre.
>
> I wrote to ‘user@cassandra.apache.org’.
>
> Re the actual problem: I am aware of the fact that C* does not store (need
> not store) the timezone as it is persisted as a Unix epoche timestamp. Not
> delivering a timezone in the JSON text representation would be OKish if the
> text representation would be guaranteed to be in UTC. But it is not. It is
> in some timezone determined by the locale of the server side or that of the
> client VM. That way it is a pain in two ways as
>
> a) I have to add the timezone in a post-processing step to all timestamps
> in my JSON responses and
> b) I also have to do some guesswork at what the actual timezone might be
>
> If there is no way to control the formatting of JSON timestamps and to add
> the time zone information, then IMHO that is bug. Is it not? Or am I
> missing something here?
>
>
> Thanks!
> Ralf
>
>
> On 08.02.2016, at 12:06, Alexandre Dutra <al...@datastax.com>
> wrote:
>
> Hello Ralf,
>
> First of all, Cassandra stores timestamps without timezone information, so
> it's not possible to retrieve the original timezone used when inserting the
> value.
>
> CQLSH uses the python driver behind the scenes, and my guess is that the
> timestamp formatting is being done driver-side – hence the timezone – while
> when you call toJson(), the formatting has to be done server-side.
>
> That said, it does seem that Cassandra is using a format without timezone
> when converting timestamps to JSON format:
>
> https://github.com/apache/cassandra/blob/cassandra-3.0/src/java/org/apache/cassandra/serializers/TimestampSerializer.java#L52
>
> I agree with you that a date format that would include the timezone would
> be preferable here, but that is a question you should ask in the Cassandra
> Users mailing list instead.
>
> Hope that helps,
>
> Alexandre
>
>
>
>
> On Mon, Feb 8, 2016 at 11:09 AM Ralf Steppacher <ra...@gmail.com>
> wrote:
>
>> Hello all,
>>
>> When I select a timestamp as JSON from Cassandra, the string
>> representation lacks the timezone information, both via CQLSH and the Java
>> Driver:
>>
>> cqlsh:events> select toJson(created_at) AS created_at from
>> event_by_patient_timestamp ;
>>
>> created_at
>> ---------------------------
>> "2016-01-04 16:05:47.123"
>>
>> (1 rows)
>>
>> vs.
>>
>> cqlsh:events> select created_at FROM event_by_user_timestamp ;
>>
>> created_at
>> --------------------------
>> 2016-01-04 15:05:47+0000
>>
>> (1 rows)
>> cqlsh:events>
>>
>> To make things even more complicated the JSON timestamp is not returned
>> in UTC. Is there a way to either tell the driver/C* to return the JSON date
>> in UTC or add the timezone information (much preferred) to the text
>> representation of the timestamp?
>>
>>
>> Thanks!
>> Ralf
>
> --
> Alexandre Dutra
> Driver & Tools Engineer @ DataStax
>
>
> --
Alexandre Dutra
Driver & Tools Engineer @ DataStax
Re: SELECT JSON timestamp lacks timezone information
Posted by Ralf Steppacher <ra...@gmail.com>.
Hi Alexandre.
I wrote to ‘user@cassandra.apache.org’.
Re the actual problem: I am aware of the fact that C* does not store (need not store) the timezone as it is persisted as a Unix epoche timestamp. Not delivering a timezone in the JSON text representation would be OKish if the text representation would be guaranteed to be in UTC. But it is not. It is in some timezone determined by the locale of the server side or that of the client VM. That way it is a pain in two ways as
a) I have to add the timezone in a post-processing step to all timestamps in my JSON responses and
b) I also have to do some guesswork at what the actual timezone might be
If there is no way to control the formatting of JSON timestamps and to add the time zone information, then IMHO that is bug. Is it not? Or am I missing something here?
Thanks!
Ralf
> On 08.02.2016, at 12:06, Alexandre Dutra <al...@datastax.com> wrote:
>
> Hello Ralf,
>
> First of all, Cassandra stores timestamps without timezone information, so it's not possible to retrieve the original timezone used when inserting the value.
>
> CQLSH uses the python driver behind the scenes, and my guess is that the timestamp formatting is being done driver-side – hence the timezone – while when you call toJson(), the formatting has to be done server-side.
>
> That said, it does seem that Cassandra is using a format without timezone when converting timestamps to JSON format:
> https://github.com/apache/cassandra/blob/cassandra-3.0/src/java/org/apache/cassandra/serializers/TimestampSerializer.java#L52 <https://github.com/apache/cassandra/blob/cassandra-3.0/src/java/org/apache/cassandra/serializers/TimestampSerializer.java#L52>
>
> I agree with you that a date format that would include the timezone would be preferable here, but that is a question you should ask in the Cassandra Users mailing list instead.
>
> Hope that helps,
>
> Alexandre
>
>
>
>
> On Mon, Feb 8, 2016 at 11:09 AM Ralf Steppacher <ralf.vivates@gmail.com <ma...@gmail.com>> wrote:
> Hello all,
>
> When I select a timestamp as JSON from Cassandra, the string representation lacks the timezone information, both via CQLSH and the Java Driver:
>
> cqlsh:events> select toJson(created_at) AS created_at from event_by_patient_timestamp ;
>
> created_at
> ---------------------------
> "2016-01-04 16:05:47.123"
>
> (1 rows)
>
> vs.
>
> cqlsh:events> select created_at FROM event_by_user_timestamp ;
>
> created_at
> --------------------------
> 2016-01-04 15:05:47+0000
>
> (1 rows)
> cqlsh:events>
>
> To make things even more complicated the JSON timestamp is not returned in UTC. Is there a way to either tell the driver/C* to return the JSON date in UTC or add the timezone information (much preferred) to the text representation of the timestamp?
>
>
> Thanks!
> Ralf
> --
> Alexandre Dutra
> Driver & Tools Engineer @ DataStax
Re: SELECT JSON timestamp lacks timezone information
Posted by Alexandre Dutra <al...@datastax.com>.
Hello Ralf,
First of all, Cassandra stores timestamps without timezone information, so
it's not possible to retrieve the original timezone used when inserting the
value.
CQLSH uses the python driver behind the scenes, and my guess is that the
timestamp formatting is being done driver-side – hence the timezone – while
when you call toJson(), the formatting has to be done server-side.
That said, it does seem that Cassandra is using a format without timezone
when converting timestamps to JSON format:
https://github.com/apache/cassandra/blob/cassandra-3.0/src/java/org/apache/cassandra/serializers/TimestampSerializer.java#L52
I agree with you that a date format that would include the timezone would
be preferable here, but that is a question you should ask in the Cassandra
Users mailing list instead.
Hope that helps,
Alexandre
On Mon, Feb 8, 2016 at 11:09 AM Ralf Steppacher <ra...@gmail.com>
wrote:
> Hello all,
>
> When I select a timestamp as JSON from Cassandra, the string
> representation lacks the timezone information, both via CQLSH and the Java
> Driver:
>
> cqlsh:events> select toJson(created_at) AS created_at from
> event_by_patient_timestamp ;
>
> created_at
> ---------------------------
> "2016-01-04 16:05:47.123"
>
> (1 rows)
>
> vs.
>
> cqlsh:events> select created_at FROM event_by_user_timestamp ;
>
> created_at
> --------------------------
> 2016-01-04 15:05:47+0000
>
> (1 rows)
> cqlsh:events>
>
> To make things even more complicated the JSON timestamp is not returned in
> UTC. Is there a way to either tell the driver/C* to return the JSON date in
> UTC or add the timezone information (much preferred) to the text
> representation of the timestamp?
>
>
> Thanks!
> Ralf
--
Alexandre Dutra
Driver & Tools Engineer @ DataStax