How to obtain and then compare two timestamp values from postgreSQL via python?

Question

I am currently using psycopg2 and python to interact with a postgreSQL database. I am in a database named "test", and have two tables: "test1" and "test2".

There is one row in each table, and the row has two columns:

Date, which has the current_date value
Timestamp, which has the current_timestamp value.

I used the following code to search for it:

 """SELECT timestamp 
FROM test1 
WHERE date = '2014-07-16';"""

and if I input this, the terminal gives:

         timestamp          
----------------------------
 2014-07-16 16:10:22.380059
(1 row)

Now, I want to look at one timestamp, and have my program tell me if it is more than 30 minutes greater than the other. However, I am having trouble obtaining these values to use in python, and figuring out how comparing works in general. I am completely new to postgreSQL, so I am not too familiar with its syntax, so I was having lots of trouble trying to assign the result of a SELECT command to a variable, which I could then compare using python.

Any and all help is appreciated!

Thanks.

You can use the datetime library to compare timestamps well. Can you show a few example rows? — Cory Kramer
– Cory Kramer, Commented Jul 16, 2014 at 23:26

wookie919 · Accepted Answer · 2014-07-17 00:22:40Z

2

I have only used Python with MySQL but postgreSQL doesn't seem too different:

>>> cur.execute("SELECT timestamp FROM test1 WHERE date = '2014-07-16'")
>>> cur.fetchone()

Now, from the documentation, it looks like the timestamp retrieved from the DB may automatically be converted to the datetime object in Python, which is convenient. But even if it is given as a string, that is still not a problem:

>>> from datetime import datetime
>>> import time
>>> d = datetime.strptime('2014-07-16 16:10:22.380059', '%Y-%m-%d %H:%M:%S.%f')
>>> time.mktime(d.timetuple())
1405483822.0

Convert both datetimes to timestamps and subtract to check if the difference is greater than 1800 (30 * 60) seconds. Microseconds are lost in the process but this level of granularity is probably not required.

edited Jul 17, 2014 at 0:22

answered Jul 17, 2014 at 0:10

wookie919

3,13428 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jj172 Over a year ago

Ok, great, it was the command cur.fetchone() I was looking for. Thanks!!

khampson · Accepted Answer · 2014-07-17 00:48:16Z

Since in this case you know both values you want to compare within Postgres and within the same database, I would recommend doing the comparison right in pure SQL.

i.e.

Given these tables:

create table foo
(
  id serial,
  created timestamp without time zone
);

create table bar
(
  id serial,
  created timestamp without time zone
);

And this data:

insert into foo (created) VALUES
('2014-07-16 16:10:22.380059'),
('2014-07-16 17:10:21.480048'),
('2014-07-16 16:32:23.580037');


insert into bar (created) VALUES
('2014-07-16 16:44:22.380059'),
('2014-07-16 17:15:21.480048'),
('2014-07-16 16:38:23.580037');

You could do a query of this form:

select ( (select created from bar order by id limit 1) -
         (select created from foo order by id limit 1)
       ) > interval '30 minutes' as time_diff;

Where the details of how you get each column can vary according to your needs.

And then back in your Python code, you would get back a 0 (if not > 30 min) or 1 if it was > 30 min.

SQL Fiddle

Collectives™ on Stack Overflow

How to obtain and then compare two timestamp values from postgreSQL via python?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related