Better way to generate dates from start date and end date in POSTGRES

Question

def db_connect():
   #Please assign variables in dict db = {"database":"", ...}
    try:
        conn = psycopg2.connect(database=db["database"], user=db["user"], password=db["password"], host=db["host"], port=db.get("port", "5432"))
    except:
        conn = None
        print "I am unable to connect to the database"
    return conn

def db_query_time():
    conn = db_connect()
    if conn is not None:
        cur = conn.cursor()
        query_generate_date_series = '''SELECT to_char(day, 'YYYYMMDD') as day_f FROM generate_series( '2017-10-23'::timestamp,'2017-10-29'::timestamp, '1 day'::interval) day ;'''
        cur.execute(query_generate_date_series)
        rows = cur.fetchall()
        print rows

Output looks like this: [('20171023',), ('20171024',), ('20171025',), ('20171026',), ('20171027',), ('20171028',), ('20171029',)]

I want dates to be in a list format. and I don't like how we are returning tuple and 2 commas instead of 1 comma. Can anyone please explain me what is the reason behind and how to fix this?

Note: Need postgres DB to run this.

Is this for a homework? Because this would be handled better in application code than through a DB. — Nic3500
– Nic3500, Commented Oct 31, 2017 at 23:05
No, Not home work. What is the library I can use in python? I tried datetime? — Raj
– Raj, Commented Nov 1, 2017 at 4:03

Dan · Accepted Answer · 2017-10-31 23:10:26Z

1

Perhaps it's because you're using a table "day" and not a column.

This might work:

SELECT to_char(generate_series( '2017-10-23'::timestamp,'2017-10-29'::timestamp, '1 day'::interval), 'YYYYMMDD') AS day_f

answered Oct 31, 2017 at 23:10

Dan

1,9211 gold badge16 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Better way to generate dates from start date and end date in POSTGRES

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related