Hello,
I have a column in my database test data and which is defined as timestamp. For Ex the data in that column is saved as 2015-07-24 12:21:00
I need to extract all the data from that table where the Test data is between 2015-07-24 12:00:00 and 2015-07-24 13:00:00
I need to see all the data that has transactions between 1 hour. How to use that in the where clause.
When i use eqaul to >= or <= it is extracting all the data for that date but not at that particular time on that date.
Thanks in advance
Mani
Calculating difference between two timestamps
Moderator: NorbertKrupa
-
- Newbie
- Posts: 2
- Joined: Mon Jul 27, 2015 9:08 pm
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Calculating difference between two timestamps
Did you try the BETWEEN operator?
Code: Select all
dbadmin=> select * from test;
c
---------------------
2015-07-24 12:21:00
2015-07-25 12:21:00
2015-07-24 12:00:00
2015-07-24 13:00:00
(4 rows)
dbadmin=> select * from test where c between '2015-07-24 12:00:00' and '2015-07-24 13:00:00';
c
---------------------
2015-07-24 12:00:00
2015-07-24 12:21:00
2015-07-24 13:00:00
(3 rows)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 2
- Joined: Mon Jul 27, 2015 9:08 pm
Re: Calculating difference between two timestamps
I used between operator but it is not working.
If I use >= also it is taking only date into consideration not the time. So if say test_date >='2015-07-24 12:00:00' i am getting all the data from that day instead of filtering from 12:00:00 on 2015-07-24.
If I use >= also it is taking only date into consideration not the time. So if say test_date >='2015-07-24 12:00:00' i am getting all the data from that day instead of filtering from 12:00:00 on 2015-07-24.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Calculating difference between two timestamps
It should, as you can see in my example. Can you describe your table (with the \d meta-command) and post the results? And can you post the exact SQL you are trying?
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Calculating difference between two timestamps
Hi!
It shouldmanialluri wrote:If I use >= also it is taking only date into consideration not the time.
Probably implicit data type coercion. If LEFT SIDE(test_date) is from data type DATE so value '2015-07-24 12:00:00' will be converted to DATE. Try next:manialluri wrote:So if say test_date >='2015-07-24 12:00:00' i am getting all the data from that day instead of filtering from 12:00:00 on 2015-07-24.
Code: Select all
test_date::TIMESTAMP >= '2015-07-24 12:00:00'::TIMESTAMP