65

I am trying to insert into my CQL table from the command line. I am able to insert everything. But I am wondering if I have a timestamp column, then how can I insert into timestamp column from the command line? Basically, I want to insert current timestamp whenever I am inserting into my CQL table -

Currently, I am hardcoding the timestamp whenever I am inserting into my below CQL table -

CREATE TABLE TEST (ID TEXT, NAME TEXT, VALUE TEXT, LAST_MODIFIED_DATE TIMESTAMP, PRIMARY KEY (ID));

INSERT INTO TEST (ID, NAME, VALUE, LAST_MODIFIED_DATE) VALUES ('1', 'elephant',  'SOME_VALUE', 1382655211694);

Is there any way to get the current timestamp using some predefined functions in CQL so that while inserting into above table, I can use that method to get the current timestamp and then insert into above table?

3 Answers3

116

You can use the timeuuid functions now() and dateof() (or in later versions of Cassandra, toTimestamp()), e.g.,

INSERT INTO TEST (ID, NAME, VALUE, LAST_MODIFIED_DATE)
                  VALUES ('2', 'elephant',  'SOME_VALUE', dateof(now()));

The now function takes no arguments and generates a new unique timeuuid (at the time where the statement using it is executed). The dateOf function takes a timeuuid argument and extracts the embedded timestamp. (Taken from the CQL documentation on timeuuid functions).

Cassandra >= 2.2.0-rc2

dateof() was deprecated in Cassandra 2.2.0-rc2. For later versions you should replace its use with toTimestamp(), as follows:

INSERT INTO TEST (ID, NAME, VALUE, LAST_MODIFIED_DATE)
                  VALUES ('2', 'elephant',  'SOME_VALUE', toTimestamp(now()));
Community
  • 1
  • 1
lorcan
  • 3,280
  • 3
  • 24
  • 31
  • @lorcan: I might also be using timeuuid functions in my question [here](http://stackoverflow.com/questions/19602979/how-to-retrieve-only-the-information-that-got-changed-from-cassandra).. Can you help me on my question as I believe you might be able to help me on my question.. – AKIWEB Oct 28 '13 at 17:26
  • how to change default timestamp...timestamp always comes with timezone in cassandra..but I don't want timezone in timestamp..so I want to change the default format..is there anyway to change? – User12345 Mar 22 '14 at 16:43
  • perfect! just the kind of function I was looking for. – asgs Jun 07 '15 at 10:16
  • My local machine timezone is IST, however, the timestamp takes UTC timezone and stores the value. what could be wrong? – bhargavaravalik Jul 26 '17 at 05:50
17

In new version of cassandra could use toTimestamp(now()), and note that function dateof is deprecated.

e.g

insert into dummy(id, name, size, create_date) values (1, 'Eric', 12, toTimestamp(now()));
Eric
  • 22,183
  • 20
  • 145
  • 196
  • "In new(er) version(s) of Cassandra" — what version is that? 3.0 or was that supported in 2.x already? – Alexis Wilke Mar 27 '16 at 00:28
  • 1
    @AlexisWilke `toTimestamp()` is supported in 3.0 for sure, but it seems it's since 2.2, you can check: https://www.instaclustr.com/blog/2015/12/18/5-things-you-need-to-know-about-cassandra-2-2/ – Eric Mar 27 '16 at 04:53
  • toTimestamp() function is not available in cassandra version 2.1.8 atleast. I just verified. – Vinod Jayachandran Apr 06 '16 at 05:06
0

There are actually 2 different ways for different purposes to insert the current timestamp. From the docs:

Inserting the current timestamp

Use functions to insert the current date into date or timestamp fields as follows:

Current date and time into timestamp field: toTimestamp(now()) sets the timestamp to the current time of the coordinator.

Current date (midnight) into timestamp field: toTimestamp(toDate(now())) sets the timestamp to the current date beginning of day (midnight).

J. Doe
  • 12,159
  • 9
  • 60
  • 114