[ https://issues.apache.org/jira/browse/CASSANDRA-10428?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14939157#comment-14939157 ]
Stefania edited comment on CASSANDRA-10428 at 10/1/15 1:42 AM: --------------------------------------------------------------- We could add microseconds but it's not as trivial as changing the default format. On 2.1 HEAD, after setting {{DEFAULT_TIME_FORMAT}} to {{'%Y-%m-%d %H:%M:%S.%f%z'}}, I tested a bit with this simplified schema: {code} CREATE TABLE ks.test ( id text, time timestamp, val text, PRIMARY KEY (id, time) ) {code} With these inserts: {code} INSERT into test (id, time, val) VALUES ( '1', '2015-09-29 20:54:23-0700', 'abc'); INSERT into test (id, time, val) VALUES ( '1', '2015-09-29 20:54:24.100-0700', 'abc'); INSERT into test (id, time, val) VALUES ( '1', '2015-09-29 20:54:24.200', 'abc'); {code} here is what's displayed: {code} SELECT * from test where id='1' and time >= '2015-09-29 20:54:24'; id | time | val ----+---------------------------------+----- 1 | 2015-09-29 12:54:24.000000+0000 | abc 1 | 2015-09-30 03:54:23.000000+0000 | abc 1 | 2015-09-30 03:54:24.000000+0000 | abc {code} We cannot support micros at present and we fix the TZ to UTC (CASSANDRA-1000). In _formatting.py_, {{format_value_timestamp}} calls {{calendar.timegm()}} which looses sub-second precision and converts to UTC. We also use a helper method from the driver, {{datetime_from_timestamp}} which currently expects seconds. So we'd have to manually add the microseconds before formatting the time. Given that users already can change {{time_format}} we should probably add microseconds. was (Author: stefania): We could add microseconds but it's not as trivial as changing the format. On 2.1 HEAD, after setting {{DEFAULT_TIME_FORMAT}} to {{'%Y-%m-%d %H:%M:%S.%f%z'}}, I tested a bit with this simplified schema: {code} CREATE TABLE ks.test ( id text, time timestamp, val text, PRIMARY KEY (id, time) ) {code} With these inserts: {code} INSERT into test (id, time, val) VALUES ( '1', '2015-09-29 20:54:23-0700', 'abc'); INSERT into test (id, time, val) VALUES ( '1', '2015-09-29 20:54:24.100-0700', 'abc'); INSERT into test (id, time, val) VALUES ( '1', '2015-09-29 20:54:24.200', 'abc'); {code} here is what's displayed: {code} SELECT * from test where id='1' and time >= '2015-09-29 20:54:24'; id | time | val ----+---------------------------------+----- 1 | 2015-09-29 12:54:24.000000+0000 | abc 1 | 2015-09-30 03:54:23.000000+0000 | abc 1 | 2015-09-30 03:54:24.000000+0000 | abc {code} We cannot support micros at present and we fix the TZ to UTC (CASSANDRA-1000). In _formatting.py_, {{format_value_timestamp}} calls {{calendar.timegm()}} which looses sub-second precision and converts to UTC. We also use a helper method from the driver, {{datetime_from_timestamp}} which currently expects seconds. So we'd have to manually add the microseconds before formatting the time. Given that users already can change {{time_format}} we should probably add microseconds. > select with exact date is not working > ------------------------------------- > > Key: CASSANDRA-10428 > URL: https://issues.apache.org/jira/browse/CASSANDRA-10428 > Project: Cassandra > Issue Type: Bug > Components: Tools > Environment: OSX 10.10.2 > Reporter: Chandran Anjur Narasimhan > Labels: cqlsh > Fix For: 2.1.x > > > Query with >= timestamp works. But the exact timestamp value is not working. > {noformat} > NCHAN-M-D0LZ:bin nchan$ ./cqlsh > Connected to CCC Multi-Region Cassandra Cluster at <host>:<port>. > [cqlsh 5.0.1 | Cassandra 2.1.7 | CQL spec 3.2.0 | Native protocol v3] > Use HELP for help. > cqlsh> > {noformat} > {panel:title=Schema|borderStyle=dashed|borderColor=#ccc|titleBGColor=#F7D6C1|bgColor=#FFFFCE} > cqlsh:ccc> desc COLUMNFAMILY ez_task_result ; > CREATE TABLE ccc.ez_task_result ( > submissionid text, > ezid text, > name text, > time timestamp, > analyzed_index_root text, > ... > ... > PRIMARY KEY (submissionid, ezid, name, time) > {panel} > {panel:title=Working|borderStyle=dashed|borderColor=#ccc|titleBGColor=#F7D6C1|bgColor=#FFFFCE} > cqlsh:ccc> select submissionid, ezid, name, time, state, status, > translated_criteria_status from ez_task_result where > submissionid='760dd154670811e58c04005056bb6ff0' and > ezid='760dd6de670811e594fc005056bb6ff0' and name='run-sanities' and > time>='2015-09-29 20:54:23-0700'; > submissionid | ezid | name > | time | state | status | > translated_criteria_status > ----------------------------------+----------------------------------+--------------+--------------------------+-----------+-------------+---------------------------- > 760dd154670811e58c04005056bb6ff0 | 760dd6de670811e594fc005056bb6ff0 | > run-sanities | 2015-09-29 20:54:23-0700 | EXECUTING | IN_PROGRESS | > run-sanities started > (1 rows) > cqlsh:ccc> > {panel} > {panel:title=Not > working|borderStyle=dashed|borderColor=#ccc|titleBGColor=#F7D6C1|bgColor=#FFFFCE} > cqlsh:ccc> select submissionid, ezid, name, time, state, status, > translated_criteria_status from ez_task_result where > submissionid='760dd154670811e58c04005056bb6ff0' and > ezid='760dd6de670811e594fc005056bb6ff0' and name='run-sanities' and > time='2015-09-29 20:54:23-0700'; > submissionid | ezid | name | time | analyzed_index_root | analyzed_log_path > | clientid | end_time | jenkins_path | log_file_path | path_available | > path_to_task | required_for_overall_status | start_time | state | status | > translated_criteria_status | type > --------------+------+------+------+---------------------+-------------------+----------+----------+--------------+---------------+----------------+--------------+-----------------------------+------------+-------+--------+----------------------------+------ > (0 rows) > cqlsh:ccc> > {panel} -- This message was sent by Atlassian JIRA (v6.3.4#6332)