supplying TZ info when saving the DateTime -> timestamp

[Peter Troshin]

Hi Guys, 

"If no time zone is specified, the time zone of the Cassandra coordinator node handing the write request is used. For accuracy, DataStax recommends specifying the time zone rather than relying on the time zone configured on the Cassandra nodes."

from http://docs.datastax.com/en/cql/3.1/cql/cql_reference/timestamp_type_r.html

So I'd like to provide TZ information when saving joda.DateTime instance (which is TZ aware) to the database "timestamp" column. 

The temporal mappings that driver seems to be supporting are these: 

TIMESTAMP <-> java.util.Date                     : use getTimestamp()

TIME      <-> long                               : use getTime()

DATE      <-> com.datastax.driver.core.LocalDate : use getDate()

(from https://groups.google.com/a/lists.datastax.com/forum/#!topic/java-driver-user/pAoLlEzmGWA)

Yet none of these are TZ aware. java.util.Date does not have a time zone information, neither does java.sql.Timestamp 

The timestamp documentation page (http://docs.datastax.com/en/cql/3.1/cql/cql_reference/timestamp_type_r.html) seem to be suggesting that we could use a properly formatted String that would represent time with TZ (e.g. "yyyy-MM-dd'T'hh:mm:ssZ" ), but the Java driver does not have String < - > timestamp codec out of the box. While it should be possible to write such a codec I feel that such a standard operation must have been done already.  

Can you suggest a way to supply date time with TZ to save into timestamp column in Cassandra via Java driver? 

Thank you for your help. 

Regards

Peter

[Kevin Gallardo]

Hi, 

The driver provides some extra codecs to deal with libraries like Joda time. Particularly, there is one Codec that we provide that may be the easiest solution, the InstantCodec, that allows you to map timestamp columns to a Joda time Instant. From there you can either start using Instant in your code directly when inserting the data, or use the toInstant() method of DateTime. Like what follows:

        Cluster cluster = Cluster.builder()

                .addContactPoint("localhost").build();

        Session session = cluster.connect();

        cluster.getConfiguration().getCodecRegistry().register(InstantCodec.instance);

        // created the table with "CREATE TABLE tstest (t text primary key, ts timestamp);"

        PreparedStatement ps = session.prepare("insert into test.tstest (t, ts) values (:t, :ts)");

        DateTime dt = DateTime.parse("2010-06-30T01:20:30+0800");

        BoundStatement bs = ps.bind();

        bs.setString("t", "4");

        bs.set("ts", dt.toInstant(), Instant.class);

        // Alternatively:

        // bs.set("ts", Instant.parse("2010-06-30T01:20:30+0800"), Instant.class)

        session.execute(bs);

Important to note: Since Cassandra's timestamp type preserves only milliseconds since epoch, it only uses the timezone provided to convert to the same time for all the timestamp columns. If you want to save a time and also save the timezone, the easiest way is to store it in a Tuple of <Timestamp, String> where the String is the Timezone. If such column is created in Cassandra, the driver then provides an extra codec to use that Tuple column with a Joda time DateTime automatically. The codec is DateTimeCodec. And there is some example on how to use it there.

Hope that helps,

Thanks.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

--

Kévin Gallardo.

kgdo.me

Software Engineer in Drivers and Tools Team at DataStax.

[P. Troshin]

Hi Kevin, 

Thanks a lot for your help. We do not really need to store the TZ, but we dislike the fact that Cassandra converts the dates to the system TZ. Though I am no longer sure what does the conversion, is this the Datastax driver of the Cassandra itself? 

Is there a way to prevent this automatic conversion? 

What happens is that we are saving dates in UTC, but I guess what happens is that because we are using java.util.Date to do that, Cassandra does not receive the TZ information assumes that we are saving date in local TZ and converts it accordingly. This leads to incorrect dates as a result.  

 

Thank you for your help. 

Regards

Peter

[Alexandre Dutra]

Hello Peter,

I'm not sure I understand your problem. Let me clarify a few points:

1. The documentation you referred to in your first message is about timestamp literals; in other words, it's about how to represent a timestamp in a CQL query. It does not apply to values sent by the driver separately from the CQL query (as bound parameters) – which is what I assume you are using.
   
2. The timestamp type in C* is really a point in time (an "instant" if you prefer), and does not care about timezones. The only thing that gets stored in C* is the number of milliseconds since the Epoch, so if you send the correct number, there is no way this value can be corrupted or misinterpreted. When you say "Cassandra converts the dates to the system TZ" – this is only true for timestamp literals that lack of timezone information, not for bound parameters. Otherwise, there is no "automatic conversion".
   
3. The java driver indeed maps timestamp columns to java.util.Date, see here for the corresponding setter method in BoundStatement. Note that java.util.Date is intended to be a thin wrapper around the amount of milliseconds since the Epoch; its methods that deal with timezones are deprecated as users should now use either java.util.Calendar or the new java.time.ZonedDateTime if timezone information is required.
   

Consequently, if you are experiencing problems with timestamps, it cannot come from neither the driver nor C*, but probably from 1) a misuse of the java.util.Date API or 2) by ill-formatted timestamp literals in your CQL queries. 

Can you share some code with us? This way we may be able to spot where the problem is.

Thanks,

Alexandre

  

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--

Kévin Gallardo.

kgdo.me

Software Engineer in Drivers and Tools Team at DataStax.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--

Alexandre Dutra

Driver & Tools Engineer @ DataStax

[P. Troshin]

Hi Guys, 

Thank you for your answers. Not sure if the code sharing do any good. We use joda DateTime with UTC TZ throughout and for saving it to Cassandra we convert it to java.util.Date. 

The issue is that if Cassandra server set to UTC, than the records created with the correct datetime (stored in timestamp).

If the server is in the other TZ, than all datetime records are shifted by TZ.  

So the question is how we could avoid this without having to supply the TZ to the server? 

Thanks

Peter

   

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

--

Kévin Gallardo.

kgdo.me

Software Engineer in Drivers and Tools Team at DataStax.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

--

Alexandre Dutra

Driver & Tools Engineer @ DataStax

--

You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

[Alexandre Dutra]

Hi again,

Sorry to insist, but what you describe is impossible. 

You are either sending bound parameters, in which case, provided that the Date instances are correct, there is no way they could get corrupted by the driver or server-side; or you are sending your timestamps as CQL literals, in which case, provided that they contain an RFC-822 4-digit time zone, there is no way either they could get corrupted by the driver or server-side.

My initial guess was that corruption was being introduced upstream, and that's why I suggested code sharing (or at least an example of INSERT query), but since you think it would be worthless, I'm afraid I cannot help you any further.

Good luck,

Alexandre

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--

Kévin Gallardo.

kgdo.me

Software Engineer in Drivers and Tools Team at DataStax.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--

Alexandre Dutra

Driver & Tools Engineer @ DataStax

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

[Kevin Gallardo]

Hi,

To clarify: 

The issue is that if Cassandra server set to UTC, than the records created with the correct datetime (stored in timestamp).
If the server is in the other TZ, than all datetime records are shifted by TZ.  

This in particular is not possible. Whatever the location or TimeZone of the Cassandra node is, the time will always be stored in UTC. More precisely, Cassandra uses Unix timestamps. So when you observe a shift in time related to TimeZones, it can only come from how you display/get the time, or how you insert it, so from the client side. Some examples here and there.

Hope that it's clearer, and if you want to share the code of how you insert the data, or retrieve it, we could look more into what causes the shift.

Thanks.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

--

Kévin Gallardo.

kgdo.me

Software Engineer in Drivers and Tools Team at DataStax.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

--

Alexandre Dutra

Driver & Tools Engineer @ DataStax

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

--

Alexandre Dutra

Driver & Tools Engineer @ DataStax

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-user+unsubscribe@lists.datastax.com.

[Gopi Polisetti]

Hi,

 In cassandra timestamp values saved as with "TZ" characters in date . Below is my code 

  val currentDateTime=new java.util.Date()

  val currentUTCMilliSeconds =new org.joda.time.DateTime(currentDateTime,DateTimeZone.UTC).getMillis

val cassandraSavingTimeStampMilli =  new java.sql.Timestamp(currentUTCMilliSeconds)

collection.saveToCassandra("gainersandlosers", "dgllistexchangeindex_tbl", SomeColumns("dailygainerloserlistid", "contentdelayed", "dailygainerloserlistjson", "dailygainerlosertimestamp"))

In intelliJ am able to see correct datetime stamp when i print but in after saving in cassandra if I check DateTime stamp values showing "TZ 

OutPut  Intelliji Println
2018-06-14 17:22:42.95

Cassandra timestamp values saved as 

"2018-06-14T11:52:42Z"

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--

Kévin Gallardo.

kgdo.me

Software Engineer in Drivers and Tools Team at DataStax.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--

Alexandre Dutra

Driver & Tools Engineer @ DataStax

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

--

Alexandre Dutra

Driver & Tools Engineer @ DataStax

--
You received this message because you are subscribed to the Google Groups "DataStax Java Driver for Apache Cassandra User Mailing List" group.

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.