Adding a time unit to a timestamp using SQL and python

Question

I am trying to add a number of hours to a timestamp in the following method in python

def add_hours(date, hour, duration):

    conn = database_connect()
    cur = conn.cursor()
    val = None

    start_time = date+" "+hour+":00:00"

    try:
       cur.execute("""SELECT timestamp %s + interval '%s hour' """,(start_time, duration))
       val = cur.fetchall()
    except:
       print("fetcherror")

    cur.close()
    conn.close()
    return val

date and hour have been concatenated to a timestamp

(it looks like an SQL timestamp value: 2016-5-24 18:00:00)

I have looked at the postgreSQL documentation availiable here: http://www.postgresql.org/docs/8.0/static/functions-datetime.html and tested the query directly (it works fine) Clearly my mistake is in the python handling of query but I can't figure out what it is.

What am I not understanding?

Which version of PostgreSQL do you use? In the link you refer to version 8.0 which is from the dinosaur era. — java
– java, Commented May 24, 2016 at 8:20

Daniel · Accepted Answer · 2016-05-24 08:47:51Z

1

You have to provide the full interval variable, not only the number part. And best, use a datetime-object for your start_time:

start_time = datetime.datetime(2016, 05, 24, 12, 4, 0)
duration = 4
cur.execute("""SELECT timestamp %s + interval %s""", (start_time, '%s hours' % duration))
result = cur.fetchall()
# -> [(datetime.datetime(2016, 5, 24, 16, 4),)]

edited May 24, 2016 at 8:47

answered May 24, 2016 at 8:06

Daniel

42.9k4 gold badges57 silver badges82 bronze badges

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

2 Comments

Flow-MH Over a year ago

I have done start_time = datetime.datetime(year, month, day, hour, 0, 0), where year, month, day and hour are all integers. the execute statement still does not work

Daniel Over a year ago

My example results in [(datetime.datetime(2016, 5, 24, 16, 4),)], so what have you done exactly.

Community · Accepted Answer · 2017-05-23 10:28:38Z

0

@Daniel answer is correct and works for me. This is a bit simpler:

cur.execute ("""
    SELECT timestamp %s + %s * interval '1 hour' 
    """,
    (start_time, duration)
)

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered May 24, 2016 at 13:05

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

Adding a time unit to a timestamp using SQL and python

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related