Exception MapD : No match found for function signature toDateTime(<CHARACTER>)


#1

what about software will i install ??

table[table.timestamp_col < ‘2000-03-01’]
return error :
mapd.ttypes.TMapDException: TMapDException(error_msg=‘Exception: Exception occurred: org.apache.calcite.runtime.CalciteContextException: From line 3, column 21 to line 3, column 46: No match found for function signature toDateTime()’)


#2

It is a basic funtion so It would work; i don’t get in which scenario you got this error


#3


#4

you don’t need to use any function to cast a string to date/timestamp

just type select * from table where date_fied = ‘yyyy-mm-dd’ or select * from table where timestamp_field = ‘yyyy-mm-dd hh24:mi:ss’ and the engine will do an implicit cast. so in your case

select *
from table_2
where "time_sell" < '2000-02-01'

you can read the supported date/timestamp formats here

http://docs.mapd.com/latest/5_dml.html

in the paragraph Accepted Date, Time, and Timestamp Formats you will find allowed formate for date/timestamp, in the paragraph Date/Time Function Support the functions to manipulate date/timestamp fields

if you want to use an explicit cast you can add date or timestamp keyword before the constant

select * from table where date_field = date ‘2008-10-01’


#5

hello,

select sssj,t.* from ele_measure_201811 t where t.SSSJ IN (‘2018-11-01’),
why the sql no record?


#6

try this

select sssj,extract (EPOCH from sssj), extract(EPOCH from timestamp ‘2018-11-01 00:00:00’) from ele_measure_201811 limit 5;

I am assuming you are using a timestamp datatype


#7

thanks your reply .
how to explain it (1541001600、1541030400) , and how to select the ture result ,
EPOCH means what?
i have only little data about mapD.

With Regards
tracy.


#8

hi @tracysw,

an EPOCH is merely an arbitrarily chosen reference time point; as an example, the EPOCH chosen by Christians is when JC born so the years before are defined BC (negative) and the year after AD (positive).

In Unix, the EPOCH is the 01-01-1970 and is measured in seconds before and after the EPOCH; so why the system is returning different values for apparently same values?

It looks likes that when you populated your tables, you used a client running with a UTC+8 environment, but now you are launching your query from a client in a UTC+0 environment.

I guess mapd database, store timestamps data types with UTC+0’s values, so subtracting 8*3600 seconds if your client is UTC+8.

Hopes it help


#9

thank you very much.
i’m so busy recently to view. my solution is, change date type to str.


#10

Don’t worry @tracysw, everyone that work is going to be busy expecially at the end of year.

Changing datatype from date/timestamp to string could be a solution, but you won’t be able to convert back to date/ts at runtime so it’s going to limit your application.