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.du...@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 <ralf.viva...@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 <alexandre.du...@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 <ralf.viva...@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.alborghe...@datastax.com
