python脚本不断将日期转换为utc [英] python script keeps converting dates to utc

问题描述

我有以下内容:

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)

这是可行的，但是在夏令时更改之后，一切都中断了...查询返回了db上的正确数据，但是当我从python脚本运行它并创建文件时，它仍然处于UTC时间.我不知道我在做什么，这将我的日期转换回UTC.有人可以帮助我吗?我尝试将顶部的时区设置为中央时区，这样它就可以将UTC转换为中央时区，而没有运气

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';") 

我也尝试过

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

我还尝试将我的时区UTC更改为CST，但没有运气...我在网络上尝试了多种解决方案，但似乎没有任何效果.任何帮助将不胜感激！

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

推荐答案

以防万一将来有人遇到类似问题……如果您将以下内容添加到查询中，则会发现问题

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'

它将解决问题.以某种方式 UTC时区还不够，您仍然需要指定输出时区

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

这篇关于python脚本不断将日期转换为utc的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！