Datetime manipulation with TIMESTAMPADD


#1

Hi there
I want to select all records between two timestamps using timestampadd to define the interval:
so select records between date X and date X + 90 days
I was hoping this would work (in PyMapd), but it doesnt…

query =
“SELECT * FROM test1 WHERE datetimeinput between ‘2017-01-01:09:00:00’ and TIMESTAMPADD(DAY, 90, ‘2017-01-01:09:00:00’)”
dftest=con.select_ipc(query)
It doesnt seem to like the 3rd argument to TIMESTAMPADD - expecting a datetime not a char?
This however works fine
“SELECT * FROM test1 WHERE datetimeinput between ‘2017-01-01:09:00:00’ and ‘2017-03-01:09:00:00’”

Can anyone help?
Thanks in advance


#2

Hi @mkor,

Adding timestamp in front of ‘2017-01-01:09:00:00’ should fix the error.

query =
“SELECT * FROM test1 WHERE datetimeinput between ‘2017-01-01:09:00:00’ and TIMESTAMPADD(DAY, 90, timestamp ‘2017-01-01:09:00:00’)”

Regards,
Veda


#3

Hi Veda
Perfect - thanks very much!
Do you happen to know the syntax if I just want a certain timestamp returned for each of the 90 days…
so just the records where the “time part” of datetimeinput = 09:30?
Thanks again for your help
Cheers


#4

Just add a condition like this

And extract( hour from datetimeinput) =9 and extract(minute from daytime input) =30

I am not sure this is the best perfomant way to do what you hasked but it would works


#5

Thanks @aznable - works great. Is there a repository of sample code somewhere that I can pick through to find examples like this?
Cheers


#6

Thanks, @mkor,

No there isn’t, but I think you have got an excellent idea; it would be useful enriching the omnisci’s docs with examples taken from community’s q/a.

@easy @aaron_mapd what do you think about that?


#7

It’s a great idea, @aznable and @mkor. We don’t have anything like that now, but we should. Let me talk with the team about it.