Calculating difference between two timestamps

Moderator: NorbertKrupa

Post Reply
manialluri
Newbie
Newbie
Posts: 2
Joined: Mon Jul 27, 2015 9:08 pm

Calculating difference between two timestamps

Post by manialluri » Mon Jul 27, 2015 9:17 pm

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Calculating difference between two timestamps

Post by JimKnicely » Mon Jul 27, 2015 9:54 pm

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

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

manialluri
Newbie
Newbie
Posts: 2
Joined: Mon Jul 27, 2015 9:08 pm

Re: Calculating difference between two timestamps

Post by manialluri » Mon Jul 27, 2015 10:09 pm

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.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Calculating difference between two timestamps

Post by JimKnicely » Mon Jul 27, 2015 11:48 pm

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

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Calculating difference between two timestamps

Post by id10t » Tue Jul 28, 2015 9:05 am

Hi!
manialluri wrote:If I use >= also it is taking only date into consideration not the time.
It should
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.
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:

Code: Select all

test_date::TIMESTAMP >= '2015-07-24 12:00:00'::TIMESTAMP

Post Reply

Return to “New to Vertica SQL”