Page 1 of 1
Calculating difference between two timestamps
Posted: Mon Jul 27, 2015 9:17 pm
by manialluri
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
Re: Calculating difference between two timestamps
Posted: Mon Jul 27, 2015 9:54 pm
by JimKnicely
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)
Re: Calculating difference between two timestamps
Posted: Mon Jul 27, 2015 10:09 pm
by manialluri
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.
Re: Calculating difference between two timestamps
Posted: Mon Jul 27, 2015 11:48 pm
by JimKnicely
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?
Re: Calculating difference between two timestamps
Posted: Tue Jul 28, 2015 9:05 am
by id10t
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