[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15261352#comment-15261352 ] Stefania commented on CASSANDRA-11137: -- Code and tests look good: all dtests passed and failing utests were either unrelated timeouts or also failing on unpatched branches. Committed to 2.2 as 88f22b9692c6fdddf837556f13140d949afe0d28 and up-merged (with -s ours for trunk). Also added a section to NEWS.txt. Test PR closed. > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Bug > Components: CQL >Reporter: Stefania >Assignee: Alex Petrov > Fix For: 3.6, 3.0.6, 2.2.7 > > > The JSON date time string representation lacks the timezone information: > {code} > cqlsh:events> select toJson(created_at) AS created_at from > event_by_user_timestamp ; > created_at > --- > "2016-01-04 16:05:47.123" > (1 rows) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15259985#comment-15259985 ] Aleksey Yeschenko commented on CASSANDRA-11137: --- It is a bug, and something that should normally be backported. It's also potentially a breaking behaviour change for JSON consumers. That said, I think benefits of fixing the bug outweigh that risk. > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Bug > Components: CQL >Reporter: Stefania >Assignee: Alex Petrov > Fix For: 3.6 > > > The JSON date time string representation lacks the timezone information: > {code} > cqlsh:events> select toJson(created_at) AS created_at from > event_by_user_timestamp ; > created_at > --- > "2016-01-04 16:05:47.123" > (1 rows) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15259946#comment-15259946 ] Stefania commented on CASSANDRA-11137: -- I'm +1 on the dtest PR, assuming the test team is also OK with using ellipses to relax the output checks. We shouldn't backport a patch because of test limitations but, I've noticed that this ticket is classified as a bug, so back-porting it might be the correct thing to do after all. Do you agree that it should be back-ported to 2.2 and 3.0, [~iamaleksey] or [~slebresne]? > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Bug > Components: CQL >Reporter: Stefania >Assignee: Alex Petrov > Fix For: 3.6 > > > The JSON date time string representation lacks the timezone information: > {code} > cqlsh:events> select toJson(created_at) AS created_at from > event_by_user_timestamp ; > created_at > --- > "2016-01-04 16:05:47.123" > (1 rows) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15259837#comment-15259837 ] Alex Petrov commented on CASSANDRA-11137: - I've also branch changes to 2.2 and 3.0 (merged mostly seamlessly) |[2.2|https://github.com/ifesdjeen/cassandra/tree/11137-2.2]|[utest|https://cassci.datastax.com/view/Dev/view/ifesdjeen/job/ifesdjeen-11137-2.2-testall/]|[dtest|https://cassci.datastax.com/view/Dev/view/ifesdjeen/job/ifesdjeen-11137-2.2-dtest/]| |[3.0|https://github.com/ifesdjeen/cassandra/tree/11137-3.0]|[utest|https://cassci.datastax.com/view/Dev/view/ifesdjeen/job/ifesdjeen-11137-3.0-testall/]|[dtest|https://cassci.datastax.com/view/Dev/view/ifesdjeen/job/ifesdjeen-11137-3.0-dtest/]| And [opened up an PR that fix tests|https://github.com/riptano/cassandra-dtest/pull/955]. I'll track the progress in corresponding [test team issue|https://issues.apache.org/jira/browse/CASSANDRA-11650]. > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Bug > Components: CQL >Reporter: Stefania >Assignee: Alex Petrov > Fix For: 3.6 > > > The JSON date time string representation lacks the timezone information: > {code} > cqlsh:events> select toJson(created_at) AS created_at from > event_by_user_timestamp ; > created_at > --- > "2016-01-04 16:05:47.123" > (1 rows) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15251231#comment-15251231 ] Stefania commented on CASSANDRA-11137: -- LGTM, committed as ae063e806191f8285f1f3bcab068b2c4bfbc257b. The failing json dtests pass locally provided the dtest patch is applied. For future reference, you can launch a CASSCI job with a different dtest repo and branch as follows: {code} !build my-job DTEST_REPO=my-gh-repo DTEST_BRANCH=my-branch {code} I've created the dtest pull request [here|https://github.com/riptano/cassandra-dtest/pull/944], I'll let the test eng team review it before merging it. > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Bug > Components: CQL >Reporter: Stefania >Assignee: Alex Petrov > Fix For: 3.6 > > > The JSON date time string representation lacks the timezone information: > {code} > cqlsh:events> select toJson(created_at) AS created_at from > event_by_user_timestamp ; > created_at > --- > "2016-01-04 16:05:47.123" > (1 rows) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15249481#comment-15249481 ] Alex Petrov commented on CASSANDRA-11137: - It's true that adding format without big necessity for users would be an overkill, it was just first thing that came into my mind. Hope I've understood "locked down to UTC" correctly (i.e. always convert to UTC timezone). I've used the {{-MM-dd HH:mm:ss.SSSX}} (to handle timezone similarly to used UTC format). |[trunk|https://github.com/ifesdjeen/cassandra/tree/11137-trunk]|[dtests code|https://github.com/ifesdjeen/cassandra-dtest/tree/11137-trunk]|[utest|https://cassci.datastax.com/view/Dev/view/ifesdjeen/job/ifesdjeen-11137-trunk-testall/]|[dtest|https://cassci.datastax.com/view/Dev/view/ifesdjeen/job/ifesdjeen-11137-trunk-dtest/]| > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Bug > Components: CQL >Reporter: Stefania >Assignee: Alex Petrov > Fix For: 3.x > > > The JSON date time string representation lacks the timezone information: > {code} > cqlsh:events> select toJson(created_at) AS created_at from > event_by_user_timestamp ; > created_at > --- > "2016-01-04 16:05:47.123" > (1 rows) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15249343#comment-15249343 ] Alex Petrov commented on CASSANDRA-11137: - Sure, I agree too. Already done, just have to adjust several tests. > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Bug > Components: CQL >Reporter: Stefania >Assignee: Alex Petrov > > The JSON date time string representation lacks the timezone information: > {code} > cqlsh:events> select toJson(created_at) AS created_at from > event_by_user_timestamp ; > created_at > --- > "2016-01-04 16:05:47.123" > (1 rows) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15249254#comment-15249254 ] Stefania commented on CASSANDRA-11137: -- I agree with Aleksey, adding a timezone is sufficient for now. > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Bug > Components: CQL >Reporter: Stefania >Assignee: Alex Petrov > > The JSON date time string representation lacks the timezone information: > {code} > cqlsh:events> select toJson(created_at) AS created_at from > event_by_user_timestamp ; > created_at > --- > "2016-01-04 16:05:47.123" > (1 rows) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15246835#comment-15246835 ] Aleksey Yeschenko commented on CASSANDRA-11137: --- For now, really, just add the timezone, locked down to UTC, until there is enough demand to allow overriding it. > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Bug > Components: CQL >Reporter: Stefania >Assignee: Alex Petrov > > The JSON date time string representation lacks the timezone information: > {code} > cqlsh:events> select toJson(created_at) AS created_at from > event_by_user_timestamp ; > created_at > --- > "2016-01-04 16:05:47.123" > (1 rows) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15246829#comment-15246829 ] Stefania commented on CASSANDRA-11137: -- Ideally {{toJson(item, dateTimeFormat='my-format')}} but if this cannot be done with UDFs then perhaps a map of options can be supported just as easily as a single parameter? > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Bug > Components: CQL >Reporter: Stefania >Assignee: Alex Petrov > > The JSON date time string representation lacks the timezone information: > {code} > cqlsh:events> select toJson(created_at) AS created_at from > event_by_user_timestamp ; > created_at > --- > "2016-01-04 16:05:47.123" > (1 rows) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15245993#comment-15245993 ] Alex Petrov commented on CASSANDRA-11137: - We could fix it by making {{toJson}} with two arities: {{toJson(item)}} and {{toJson(item, format)}}. Format can be passed as literals when [10783|https://issues.apache.org/jira/browse/CASSANDRA-10783] is implemented. WDYT [~Stefania]? > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Bug > Components: CQL >Reporter: Stefania >Assignee: Alex Petrov > > The JSON date time string representation lacks the timezone information: > {code} > cqlsh:events> select toJson(created_at) AS created_at from > event_by_user_timestamp ; > created_at > --- > "2016-01-04 16:05:47.123" > (1 rows) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15138556#comment-15138556 ] Alexandre Dutra commented on CASSANDRA-11137: - bq. I suspect the Java driver simply prints the JSON string like cqlsh does so the fix is definitely in Cassandra. Correct. > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Improvement > Components: CQL >Reporter: Stefania > > The JSON date time string representation lacks the timezone information: > {code} > cqlsh:events> select toJson(created_at) AS created_at from > event_by_user_timestamp ; > created_at > --- > "2016-01-04 16:05:47.123" > (1 rows) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15138469#comment-15138469 ] Ralf Steppacher commented on CASSANDRA-11137: - My original issue was with the Java driver. I just used cqlsh to demonstrate the issue. (The examples in the ticket description use two different table names but they should be the same table. I cannot edit the description.) Personally I think that adding the timezone is the correct fix. Every datetime library will do the "right thing" if the information is included. > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Improvement > Components: CQL >Reporter: Stefania > > The JSON date time string representation lacks the timezone information: > {code} > 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) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CASSANDRA-11137) JSON datetime formatting needs timezone
[ https://issues.apache.org/jira/browse/CASSANDRA-11137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15138492#comment-15138492 ] Stefania commented on CASSANDRA-11137: -- bq. My original issue was with the Java driver. I suspect the Java driver simply prints the JSON string like cqlsh does so the fix is definitely in Cassandra. bq. The examples in the ticket description use two different table names but they should be the same table. I cannot edit the description. Description amended, thank you. > JSON datetime formatting needs timezone > --- > > Key: CASSANDRA-11137 > URL: https://issues.apache.org/jira/browse/CASSANDRA-11137 > Project: Cassandra > Issue Type: Improvement > Components: CQL >Reporter: Stefania > > The JSON date time string representation lacks the timezone information: > {code} > cqlsh:events> select toJson(created_at) AS created_at from > event_by_user_timestamp ; > created_at > --- > "2016-01-04 16:05:47.123" > (1 rows) > {code} > vs. > {code} > cqlsh:events> select created_at FROM event_by_user_timestamp ; > created_at > -- > 2016-01-04 15:05:47+ > (1 rows) > cqlsh:events> > {code} > To make things even more complicated the JSON timestamp is not returned in > UTC. > At the moment {{DateType}} picks this formatting string {{"-MM-dd > HH:mm:ss.SSS"}}. Shouldn't we somehow make this configurable by users or at a > minimum add the timezone? -- This message was sent by Atlassian JIRA (v6.3.4#6332)