不正确的时区打入db [英] Incorrect timezone getting punched into db
问题描述
我创建了具有带时区dateTime的列的表。
在我的 model.py 中:
db = SQLAlchemy(app)
类消息(db.Model):
__tablename__ ='messages'
msgId = db.Column('msg_id',db.Integer,primary_key = True)
fullName = db.Column(db.String(60))
message = db.Column(db.String)
email = db.Column(db.String,unique = True)
visitorId = db .Column(db.String(10))
done = db.Column(db.Boolean)
pub_date = db.Column(types.Time(timezone = True))
def __init __(self,fullName,email,message,visitorId,submitTime):
self.fullName = fullName
self.email = email
self.message = message
self。 visitorId = visitorId
self.done = False
self.pub_date = submitTime
我已经在 heroku pg:psql
中使用 db.create_all()
创建了表,结果如下:
DATABASE => \ d消息
表public.messages
Column |类型|修饰符
----------- + ----------------------- + --------- --------------------------------------------------
msg_id |整数| not null default nextval('messages_msg_id_seq':: regclass)
fullName |变化的字符(60)|
消息|字符变化|
email |字符变化|
visitorId |字符变化(10)|
完成|布尔|
pub_date |时间与时区|
无论使用什么时区/日期时间,似乎都会使用 +00
即UTC。
4 | ciasto |测试| | 9IQVW1K6W | f | 15:48:31.784704 + 00
为什么会发生这种情况?
我正在使用下面的路由函数来传递时间,它应该从下面传递正确的 dtWithZone
到postgreSQL的表消息 pub_date
@ app.route('/ newmsg',methods = ['GET','POST' ])
def newmsg():
form = _appforms.MessagingForm()
if form.validate_on_submit():
name = request.form ['fullName']
ip = request.access_route [0]#给出访问者的IP地址
unique_visitor_id = request.cookies.get(unique_visitor)
data = _utils.getJsonFromURL(http:/如果data [status] ==success:
tz = data [timezone]
flash(您的IP是{},您正在从{}时区访问此网站。 $ b dtWithZone = d atetime.datetime.now(pytz.timezone(tz))
msg =消息(name,request.form ['email'],request.form ['message'],unique_visitor_id,dtWithZone)
_db .session.add(msg)
_db.session.commit()
msg =谢谢,{}。格式(名称)
返回render_template('thankyou.html',form = form,msg = msg)
return render_template('newMessage.html',form = form,title =|消息,msg =写出你的消息)
然而,这在我的本地计算机上正常工作,看看表中的内容我可以看到:
$ p $ 8 |测试者|本地调用| | NV33A1L66 | f | 21:09:24.804903+ 05:30
要查看postgresql在其内部tz数据库中的时区,运行以下查询:
select * from pg_timezone_names;
(编辑清单)
名称|缩写| utc_offset | is_dst
------------------------------ ---- + -------- + ------------ + --------
美国/丹佛| MDT | -06:00: 00 | t
美洲/凤凰| MST | -07:00:00 | f
美洲/纽约| EDT | -04:00:00 | t
美洲/芝加哥| CDT | - 05:00:00 | t
America / Los_Angeles | PDT | -07:00:00 | t
以下是设置tz和插入时间戳的示例:
create table tztest(ts timestamptz,tz text);
set timezone =America / New_York;
插入tztest值('2017-01-10 12:00:00','America / New_York');
set timezone ='America / Chicago';
插入tztest值('2017-01-10 12:00:00','America / Chicago');
set timezone ='America / Denver';
插入tztest值('2017-01-10 12:00:00','美国/丹佛');
set timezone ='America / Phoenix';
插入tztest值('2017-01-10 12:00:00','America / Phoenix');
set timezone ='America / Los_Angeles';
插入tztest值('2017-01-10 12:00:00','America / Los_Angeles')
select * from tztest;
ts | tz
------------------------ + -------------------- -
2017-01-10 09:00:00-08 | America / New_York
2017-01-10 10:00:00-08 | America / Chicago
2017-01-10 11:00:00-08 |美国/丹佛
2017-01-10 11:00:00-08 | America / Phoenix
2017-01-10 12:00:00-08 | America / Los_Angeles
请注意,虽然洛杉矶被列为-07:00:00偏移量,不是夏天,所以现在是一月份的-08:00:00。
要查看实际存储的时间戳,请将时区设置为UTC:
set timezone ='UTC';
select * from tztest;
ts | tz
------------------------ + -------------------- -
2017-01-10 17:00:00 + 00 | America / New_York
2017-01-10 18:00:00 + 00 |美洲/芝加哥
2017-01-10 19:00:00 + 00 |美国/丹佛
2017-01-10 19:00:00 + 00 | America / Phoenix
2017-01-10 20:00:00 + 00 | America / Los_Angeles
I have created table that has column with dateTime with timezone. In my model.py:
db = SQLAlchemy(app)
class Messages(db.Model):
__tablename__ = 'messages'
msgId = db.Column('msg_id', db.Integer, primary_key=True)
fullName = db.Column(db.String(60))
message = db.Column(db.String)
email = db.Column(db.String, unique=True)
visitorId = db.Column(db.String(10))
done = db.Column(db.Boolean)
pub_date = db.Column(types.Time(timezone=True))
def __init__(self, fullName, email, message, visitorId, submitTime):
self.fullName = fullName
self.email = email
self.message = message
self.visitorId = visitorId
self.done = False
self.pub_date = submitTime
And I have created table using db.create_all()
on heroku pg:psql
that resulted below:
DATABASE=> \d messages
Table "public.messages"
Column | Type | Modifiers
-----------+-----------------------+-----------------------------------------------------------
msg_id | integer | not null default nextval('messages_msg_id_seq'::regclass)
fullName | character varying(60) |
message | character varying |
email | character varying |
visitorId | character varying(10) |
done | boolean |
pub_date | time with time zone |
No matter what timezone/date time I am using the timezone seems to getting punched +00
which is UTC.
4 | ciasto | testing | | 9IQVW1K6W | f | 15:48:31.784704+00
why is this happening?
I am passing the time as using the below routing function which should be passing correct dtWithZone
from below to postgreSQL's table messages pub_date
@app.route('/newmsg', methods=['GET', 'POST'])
def newmsg():
form = _appforms.MessagingForm()
if form.validate_on_submit():
name = request.form['fullName']
ip = request.access_route[0] # gives ip address of the visitor
unique_visitor_id = request.cookies.get("unique_visitor")
data = _utils.getJsonFromURL("http://ip-api.com/json/{}".format(ip))
tz = "Asia/Kolkata"
if data["status"] == "success":
tz = data["timezone"]
flash("Your IP is {} and you are visiting this site from {} timezone.".format(ip, tz))
dtWithZone = datetime.datetime.now(pytz.timezone(tz))
msg = Messages(name, request.form['email'], request.form['message'], unique_visitor_id, dtWithZone)
_db.session.add(msg)
_db.session.commit()
msg = "Thank you, {}".format(name)
return render_template('thankyou.html', form=form, msg=msg)
return render_template('newMessage.html', form=form, title=" | Messaging", msg="Write your message")
However this is working correctly on my local computer and looking at table entry I can see:
8 | tester | local teasing | | NV33A1L66 | f | 21:09:24.804903+05:30
So here's a short tutorial on timezones for ya. To see what timezones postgresql has in its internal tz database, run this query:
select * from pg_timezone_names ;
(edited list)
name | abbrev | utc_offset | is_dst
----------------------------------+--------+------------+--------
America/Denver | MDT | -06:00:00 | t
America/Phoenix | MST | -07:00:00 | f
America/New_York | EDT | -04:00:00 | t
America/Chicago | CDT | -05:00:00 | t
America/Los_Angeles | PDT | -07:00:00 | t
And here's an example of setting tz and inserting timestamps:
create table tztest (ts timestamptz, tz text);
set timezone="America/New_York";
insert into tztest values ('2017-01-10 12:00:00','America/New_York');
set timezone='America/Chicago';
insert into tztest values ('2017-01-10 12:00:00','America/Chicago');
set timezone='America/Denver';
insert into tztest values ('2017-01-10 12:00:00','America/Denver');
set timezone='America/Phoenix';
insert into tztest values ('2017-01-10 12:00:00','America/Phoenix');
set timezone='America/Los_Angeles';
insert into tztest values ('2017-01-10 12:00:00','America/Los_Angeles')
select * from tztest ;
ts | tz
------------------------+---------------------
2017-01-10 09:00:00-08 | America/New_York
2017-01-10 10:00:00-08 | America/Chicago
2017-01-10 11:00:00-08 | America/Denver
2017-01-10 11:00:00-08 | America/Phoenix
2017-01-10 12:00:00-08 | America/Los_Angeles
Note that while LA is listed as having a -07:00:00 offset, it's not summer, so right now it's -08:00:00 for dates January.
To see the actual stored timestamps, set the timezone to UTC:
set timezone='UTC';
select * from tztest ;
ts | tz
------------------------+---------------------
2017-01-10 17:00:00+00 | America/New_York
2017-01-10 18:00:00+00 | America/Chicago
2017-01-10 19:00:00+00 | America/Denver
2017-01-10 19:00:00+00 | America/Phoenix
2017-01-10 20:00:00+00 | America/Los_Angeles
这篇关于不正确的时区打入db的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!