python script keeps converting dates to utc

Question

I have the following:

import psycopg2
from openpyxl import Workbook
wb = Workbook()
wb.active =0
ws = wb.active
ws.title = "Repair"
ws.sheet_properties.tabColor = "CCFFCC"

print(wb.sheetnames)

import datetime
import smtplib
import mimetypes

import logging
LOG_FILENAME = 'log-production.out'
logging.basicConfig(filename=LOG_FILENAME, level=logging.DEBUG)

logging.debug('This message should go to the log file')

from datetime import date, timedelta
import os, sys
try:
    conn = psycopg2.connect("connection string")
except:
    print "I am unable to connect to the database"

cur = conn.cursor()
cur.execute("""SELECT ams.unit.line,ams.unit.work_order,ams.unit.model_num, ams.unit.revision ,ams.unit.serial_num,ams.unit.lpn, ams.unit_repair_detail.level_1_name as level_1,
          ams.unit_repair_detail.level_2_name as level_2, ams.unit_repair_detail.level_3_name as level_3,ams.unit_repair_detail.level_4_name as level_4,ams.unit_repair.date_started AT TIME ZONE 'UTC' as date_started,ams.unit_repair.date_completed AT TIME ZONE 'UTC' as date_completed
                   FROM ams.unit_repair
                        left join 
                            ams.unit
                                    on ams.unit_repair.unit_id=ams.unit.id and 
                                    LOWER(ams.unit_repair.line) =  LOWER(ams.unit.line)
                        right join 
                            ams.unit_repair_detail
                                    on ams.unit_repair.sid = ams.unit_repair_detail.unit_repair_sid
                                    WHERE 
                                     LOWER(ams.unit.line) like ('%') and 
                                      ams.unit_repair_detail.date_created  >= (CURRENT_TIMESTAMP  AT TIME ZONE 'UTC'  - interval '24 hours')
                                     AND ams.unit_repair_detail.date_created <= (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')
                                     and LOWER(ams.unit.model_num) like LOWER('%')

                                    order by  model_num asc""")
rows = cur.fetchall()
print "\nShow me the databases:\n"
col_names = ["Line","Work order","Model number","Revision","Serial number","Lpn","Level 1","Level 2","Level 3","Level 4","Date started","Date completed"]    

ws.append(col_names)
for row in rows:
    ws.append(row)

This was working but after the daylight savings time change everything broke... The query returns the correct data on the db but when I run it from the python script and the file is created it is still in UTC time. I don't know what I am doing that is converting my dates back to UTC... Can anybody help me? I have tried setting the timezones at the top to be central so it converts the UTC to central with no luck

cur.execute("SET TIME ZONE 'America/Chicago';") 

I have also tried

>>> import time
>>> offset = time.timezone if (time.localtime().tm_isdst == 0) else time.altzone
>>> offset / 60 / 60 * -1

I also tried changing my AT TIME ZONE UTC TO CST and no luck... I have tried multiple solutions on the web but nothing appears to be working. Any help will be greatly appreciated!!

Answer 1

just in case anybody runs into something like this in the future... I found the problem if you add the following to the query

 `at time zone 'America/Chicago'

it will resolve the problem. Somehow the at time zone UTC is not enough you still need to specify the output timezone