Time literals causing cast error on insert or returning different value when projected


#1

MAPD JDBC 0.1
MAPD 3.3.1-20171108-32e7bcc

select TIME ‘20:10:05’ from T is returning a value shifted by 5 hours (i.e. 15:10:05) . How can one preserve the value as stated?

attempting to insert a value using a TIME literal causes an invalid cast error

insert into TTM(RNUM, CTM) values ( 1, time ‘00:00:00’);
Error: Query failed : Exception: Invalid cast.
SQLState: null
ErrorCode: 0

having similar CAST issue with timestamp literals
insert into TTS(RNUM, CTS) values ( 1, TIMESTAMP ‘1996-01-01 00:00:00’);


#2

Hi,

All times in MapD are currently returned as GMT time.

To insert as a literal use '2017-12-22T18:39:0031-Oct-13 11:30:25 -0800`

see https://www.mapd.com/docs/latest/mapd-core-guide/dml/?highlight=timestamp#accepted-date-time-and-timestamp-formats

When inserting data use timezone specific format if you need to specify something other than GMT.

regards


#3

An ISO-SQL compliant application would consider TIME as being non-time zone bearing and thus not generate time literals with a time zone offset etc. So understand response but not what I would expect. Don’t think I saw in the docs discussion on storing at UTC.