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) print(res.fetchall())``` 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"]
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 make your query run you can
- change literals adding a time part (best performance)
- 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'
- 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
table_det=conn.get_table_details('m86775bb6_877a_47bb_b281_3c498b743516') mycolumn = [x for x in table_det if x.name == 'txn_date'] 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