Example on datetimeformat in cqlsh copy command to transfer data in/out of Cassandra

donghua@cqlsh:mykeyspace> create table t3 (id int, d timestamp, primary key(id));
donghua@cqlsh:mykeyspace> desc t3;

CREATE TABLE mykeyspace.t3 (
    id int PRIMARY KEY,
    d timestamp
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';
donghua@cqlsh:mykeyspace> insert into t3 (id,d) values (1,totimestamp(now()));
donghua@cqlsh:mykeyspace> insert into t3 (id,d) values (2,'2016-10-17T10:00:00');
donghua@cqlsh:mykeyspace> insert into t3 (id,d) values (3,'2016-10-17T10:00:00+0800');
donghua@cqlsh:mykeyspace> insert into t3 (id,d) values (4,'2016-10-17T10:00:00+0000');
donghua@cqlsh:mykeyspace> select * from t3;

 id | d
----+---------------------------------
  1 | 2016-10-17 10:20:35.416000+0000
  2 | 2016-10-17 02:00:00.000000+0000
  4 | 2016-10-17 10:00:00.000000+0000
  3 | 2016-10-17 02:00:00.000000+0000

(4 rows)

donghua@cqlsh:mykeyspace> copy t3 TO '/tmp/t3-1.csv' with header=true;
Reading options from the command line: {'header': 'true'}
Using 1 child processes

Starting copy of mykeyspace.t3 with columns [id, d].
Processed: 4 rows; Rate:       5 rows/s; Avg. rate:       4 rows/s
4 rows exported to 1 files in 0.945 seconds.


ddonghua@cqlsh:mykeyspace> copy t3 TO '/tmp/t3-2.csv' with header=true and datetimeformat='%m/%d/%Y';
Reading options from the command line: {'datetimeformat': '%m/%d/%Y', 'header': 'true'}
Using 1 child processes

Starting copy of mykeyspace.t3 with columns [id, d].
Processed: 4 rows; Rate:       5 rows/s; Avg. rate:       4 rows/s
4 rows exported to 1 files in 1.070 seconds.


[donghua@localhost ~]$ cat /tmp/t3-1.csv
id,d
2,2016-10-17 02:00:00.000+0000
1,2016-10-17 10:20:35.416+0000
3,2016-10-17 02:00:00.000+0000
4,2016-10-17 10:00:00.000+0000
[donghua@localhost ~]$ cat /tmp/t3-2.csv
id,d
2,10/17/2016
1,10/17/2016
3,10/17/2016
4,10/17/2016


donghua@cqlsh:mykeyspace> truncate table t3;
donghua@cqlsh:mykeyspace> select * from t3;

 id | d
----+---

(0 rows)
donghua@cqlsh:mykeyspace> copy t3 from '/tmp/t3-1.csv' with header=true;
Reading options from the command line: {'header': 'true'}
Using 1 child processes

Starting copy of mykeyspace.t3 with columns [id, d].
Processed: 4 rows; Rate:       4 rows/s; Avg. rate:       7 rows/s
4 rows imported from 1 files in 0.548 seconds (0 skipped).
donghua@cqlsh:mykeyspace> select * from t3;

 id | d
----+---------------------------------
  1 | 2016-10-17 10:20:35.416000+0000
  2 | 2016-10-17 02:00:00.000000+0000
  4 | 2016-10-17 10:00:00.000000+0000
  3 | 2016-10-17 02:00:00.000000+0000

(4 rows)

donghua@cqlsh:mykeyspace> truncate table t3;
donghua@cqlsh:mykeyspace> copy t3 from '/tmp/t3-2.csv' with header=true and datetimeformat='%m/%d/%Y';
Reading options from the command line: {'datetimeformat': '%m/%d/%Y', 'header': 'true'}
Using 1 child processes

Starting copy of mykeyspace.t3 with columns [id, d].
Processed: 4 rows; Rate:       5 rows/s; Avg. rate:       7 rows/s
4 rows imported from 1 files in 0.544 seconds (0 skipped).
donghua@cqlsh:mykeyspace> select * from t3;

 id | d
----+---------------------------------
  1 | 2016-10-17 00:00:00.000000+0000
  2 | 2016-10-17 00:00:00.000000+0000
  4 | 2016-10-17 00:00:00.000000+0000
  3 | 2016-10-17 00:00:00.000000+0000

(4 rows)
donghua@cqlsh:mykeyspace>