Timestamp breaking


query11 = con.get_tables()
query = "SELECT coalesce(count(transaction_id), 0) AS transaction_id, datepart('yearmonth', txn_date) AS \"year-month\" FROM m86775bb6_877a_47bb_b281_3c498b743516 WHERE txn_date BETWEEN '2017-01-01' AND '2017-12-31' GROUP BY \"year-month\" ORDER BY \"year-month\" LIMIT 501"
res = c.execute(query)

above is my code i am getting error as timestamp breaking what is this error
error: "Invalid timestamp break string 2017-01-01"

[quote="nagulan, post:1, topic:1493"]


try with
BETWEEN '2017-01-01 00:00:00' AND '2017-12-31 00:00:00'

you are getting an error because you are specifying a DATE format to fliter a TIMESTAMP field type, so the software is expecting also the time part


no field type is timestamp without time zone but if i use BETWEEN ‘2017-01-01 00:00:00’ AND ‘2017-12-31 00:00:00’ its working

so to which type can i use type as BETWEEN ‘2017-01-01’ AND ‘2017-12-31’
to use BETWEEN ‘2017-01-01’ AND ‘2017-12-31’ how the column type should be ?


I know the datatype in the table is a timestamp; it’s because of that the system is expecting a literal in timestamp format.

The formats of implicit conversione are the One specified on docs in the section accepted date time and timestamp formats


So without the time part you are specifying a date


is there any way to query timestamp field without giving time part

can i use BETWEEN ‘2017-01-01’ AND ‘2017-12-31’ this for date filed


Yes with a date field you can use those literal


to recap

to make your query run you can

  1. change literals adding a time part (best performance)
  2. cast the timestamp field in you query (performance reduced…more or less the half)
    WHERE cast(txn_date as date) BETWEEN '2017-01-01' AND '2017-12-31'
  3. change your datatype from timestamp to date

to get metadata of your tables at runtime with pymapd you have to use the get_table_details(table_name) method; so to get the datatyp for a particular column you can do this

mycolumn = [x for x in table_det if x.name == 'txn_date'][0]
print (mycolumn.type)

i guess there are more elegant ways to do this but i am not a great python programmer and i am short of time

to get metadata from mapdql you can use the \d table_name command; you will get the DDL of the table, so all the datatypes