Comparison of integers and timestamps


#1

Hi,
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”.


#2

Hi,

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

e.g.

    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

#3

It has solved my problem perfectly.Thank you very much!


#4

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).


#5

@Hacher,

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