Crate DB Timestamp query

Question

I have created a table in crate DB with timestamp column. However while inserting records into it, there is not timezone information passed along as mentioned in the docs.

insert into t1 values(2,'2017-06-30T02:21:20');

this gets stored as:

2   | 1498789280000 (Fri, 30 Jun 2017 02:21:20 GMT)

Now my queries are all failing as the timestamp has got recorded as GMT and my queries are all in localtime timezone (Asia/Kolkata)

If anyone has run into this problem, could you please let me know whats the best way to modify the column to change values from GMT to IST without losing it, It has couple of millions of important records which cannot be lost or corrupted.

cheers!

score 1 · Answer 1 · answered Jul 04 '17 at 08:18

CrateDB always assumes that timestamps are UTC when they are stored without timezone information. This is due to the internal representation as a simple long data type - which means that your timestamp is stored as a simple number: https://crate.io/docs/reference/en/latest/sql/data_types.html#timestamp CrateDB also accepts the timezone information in your ISO string, so just inserting insert into t1 values(2,'2017-06-30T02:21:20+05:30'); will convert it to the appropriate UTC value.

For records that are already stored, you can make the DB aware of the timezone when querying for the field and convert the output back by passing the corresponding timezone value into a date_trunc or date_format function: https://crate.io/docs/reference/en/latest/sql/scalar.html#date-and-time-functions

for the records that are already stored, passing the timezone information to the date_format function would not work 'coz it would then add UTC+05:30 to the already stored timestamp which would yield a wrong timestamp again. I need a way to bulk update the timestamp to (timestamp-05:30) so when I query it back using date_format with Asia/Kolkata as the tz it will return the correct result set. thanks for the reply! — Nachiketh, Jul 10 '17 at 11:45

score 0 · Answer 2 · answered Jul 17 '17 at 09:30

this UPDATE test set ts = date_format('%Y-%m-%dT%H:%i:%s.%fZ','+05:30', ts); should do it.

cr> create table test(ts timestamp);
CREATE OK, 1 row affected  (0.089 sec)
cr> insert into test values('2017-06-30T02:21:20');
INSERT OK, 1 row affected  (0.005 sec)
cr> select date_format(ts) from test;
+-----------------------------+
| date_format(ts)             |
+-----------------------------+
| 2017-06-30T02:21:20.000000Z |
+-----------------------------+
SELECT 1 row in set (0.004 sec)
cr> UPDATE test set ts = date_format('%Y-%m-%dT%H:%i:%s.%fZ','+05:30', ts);
UPDATE OK, 1 row affected  (0.006 sec)
cr> select date_format(ts) from test;
+-----------------------------+
| date_format(ts)             |
+-----------------------------+
| 2017-06-30T07:51:20.000000Z |
+-----------------------------+
SELECT 1 row in set (0.004 sec)

`

Crate DB Timestamp query

2 Answers2