I want to pick out data for a certain period of time.These data in mapd is timestamp.How can I compare it with long integer?
By the way ,the time in mapd is just like “1512315632”.
As far I know, in the query you can force an explicit cast with the keywords TIME/DATE/TIMESTAMP with a fixed format for every CAST and it’s not milliseconds from epoch but hh24:mi:ss/yyyy-mm-dd/yyyy-mm-dd hh24:mi:ss respectivly .
You can use seconds from epoch and millis from epoch with the copy command (and yes, that’s the format MAPD uses for storing date/time datatype internally)
If you App need to specify a timestamp with and Epoch, you have to use dattime functions like Interval, addtimestamp but the impact on performance can be significative
mapdql> select count() from yellow_tripdata where tpep_pickup_datetime > timestamp '2014-09-24 12:03:45'; 43360008 1 rows returned. Execution time: 6 ms, Total time: 7 ms mapdql> select count() from yellow_tripdata where tpep_pickup_datetime >DATEADD('second',1411560225,date '1970-01-01'); 43360008 1 rows returned. Execution time: 177 ms, Total time: 178 ms
It has solved my problem perfectly.Thank you very much!
The canonical (and probably the most performant) way would just be to extract the epoch from the timestamp, i.e via
extract(epoch from timestamp_column).
as @darwin suggested
select count() from yellow_tripdata where extract(epoch from tpep_pickup_datetime)>1411560225 ; mapdql> select count() from yellow_tripdata where extract(epoch from tpep_pickup_datetime)>1411560225 ; 43360008 1 rows returned. Execution time: 16 ms, Total time: 17 ms
10x performant solution than I suggested before; use extract as suggested by Darwin