CODEWITHSUNDEEP
pythonsqlpostgresqltimetimestamp
Flow-MH
Flow-MH

Reputation: 71

Adding a time unit to a timestamp using SQL and python

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?

Upvotes: 1

Views: 650

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

@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)
)

Upvotes: 0

Daniel
Daniel

Reputation: 42758

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),)]

Upvotes: 1

Related Questions