Timestamp breaking


#1


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"]

#2

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


#3

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 ?


#4

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

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

So without the time part you are specifying a date


#5

@aznable
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


#6

Yes with a date field you can use those literal


#7

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

table_det=conn.get_table_details('m86775bb6_877a_47bb_b281_3c498b743516')
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