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!!

Has nothing to do with **openpyxl**, please remove **tag openpyxl**. — stovfl, Mar 17 '17 at 16:25
What are the results you get from the command line? Are they the same than what you get from Psycopg? — Nicolás Ozimica, Mar 17 '17 at 16:57
well I am running it using IDLE but the results I get are as if I removed the AT TIME ZONE 'UTC' from the SELECT statement in the query. so for example I get dates like: on my xlsx file:2017-03-17 11:38:51 and when i run the query "2017-03-17 06:37:57.3-05" is that what you mean by results? I never get any errors... so it runs fine but the dates are off its like the conversion is being ignored — cocopan, Mar 17 '17 at 17:07
also in case it helps when i print each row i get something like this datetime.datetime(2017, 3, 16, 19, 32, 38, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-300, name=None)) — cocopan, Mar 17 '17 at 17:13

score 1 · Accepted Answer · answered Mar 17 '17 at 18:43

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

answered Mar 17 '17 at 18:43

cocopan

109
3
19

Craig Ringer has a great breakdown of the how and why of SQL standards, POSIX standards, and PostgreSQL, and how it led to this horrible mess of time zones: https://stackoverflow.com/a/21278339/4081409 – Jason Aug 19 '18 at 11:35

python script keeps converting dates to utc

1 Answers1

Linked