Store timestamp with UTC offset from string into Cassandra

Question

I want to store a timestamp data with UTC offset from string into a Cassandra timestamp column. My string is something like yyyy-MM-dd HH:mm:ssZ where Z is a string like +0100. All I want is to store it as a timestamp in the column event_time including my UTC offset. Using DataStax Java Driver:

PreparedStatement statement = session.prepare(
            "INSERT INTO " + table + "( " + frame + ", device_uuid, event_time, value)"
                    + "VALUES (?,?,?,?);");
            BoundStatement boundStatement = new BoundStatement(statement);
            session.execute(boundStatement.bind(frame_val, uuid, date, value));

where date is calculated this way:

String str = "2016-11-22 02:33:25+0100";
DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ssZ");
    Date date = null;
    try {
        date = formatter.parse(str);
    } catch (ParseException e) {
        System.out.println("Error in parsing date");
        e.printStackTrace();
    }
    return date;

The problem is, I always got values like 2016-11-25 13:24:07+0000, so with offset always set to zero; I checked here for data format https://docs.datastax.com/en/cql/3.1/cql/cql_reference/timestamp_type_r.html and I cannot understand where i am going wrong. I think the issue is that I don't want Cassandra to use its Timezone but I want to "inject" my UTC offset in a timestamp value. Can anybody point me how to do this?

Your event_time is storing correctly in Cassandra. It's just showing time in GMT. — Ashraful Islam
hi, were you able to fix this? could you get cassandra to store the offset too? i have the same problem please. thank you — Scaramouche

Richu John Richu John · Accepted Answer · 2017-01-03T06:53:39

By using Joda Time package you can easily achieve this.

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

public Class Util {
static DateTimeFormatter formatter;

    public static DateTime strToDateTime(String s, String format) {
        DateTime retValue = null;
        formatter = DateTimeFormat.forPattern(format);
        try {
            retValue = formatter.withOffsetParsed().parseDateTime(s);
        } catch (Exception e) {
            return retValue;
        }
        return retValue;
    }
}


@Test
public void strToDateTime() throws Exception {
    DateTime s = Util.strToDateTime("2016-11-22 02:33:25+0100", "yyyy-MM-dd HH:mm:ssZ");
    System.out.println(s);
}

use the withOffsetParsed() function with the formatter before trying to parse the dateTime string value.

Store timestamp with UTC offset from string into Cassandra

1 Answers