0

I'd like to use the timestamp from a database result and convert it to my locale time format. The timestamp itself is saved in UTC format: 2015-03-30 07:19:06.746037+02. After calling print value.strftime(format) with the format %d.%m.%Y %H:%M %z the output will be 30.03.2015 07:19 +0200. This might be the correct way to display timestamps with timezone information but unfortunately users here are not accustomed to that. What I want to achieve is the following for the given timestamp: 30.03.2015 09:19. Right now I'm adding two hours via

is_dst = time.daylight and time.localtime().tm_isdst > 0
utc_offset = - (tine.altzone if is_dst else time.timezone)
value = value + timedelta(seconds=utc_offset)

I was wondering if there is a more intelligent solution to my problem. (timestamp.tzinfo has a offset value, can/should this be used instead? The solution needs to be DST aware too.)

Michael
  • 57
  • 1
  • 7
  • 1
    I thought you were just being funny when you attached a "sqlalchemy" tag, little did I know it actually exists. :) – Alexander Craggs Mar 30 '15 at 10:56
  • 1
    :) It's a pretty well known ORM in the python/flask universe. – Michael Mar 30 '15 at 10:57
  • You could use [pytz](https://pypi.python.org/pypi/pytz/) if you want to do it in the python level. It is DST aware. In SQL/SQLAlchemy level you could use extract timezone info and add it to the time. – adarsh Mar 30 '15 at 10:58
  • What do you mean it is saved in UTC format? That timestamp doesn't look like it's UTC. Do you mean ISO format with timezone information? – adarsh Mar 30 '15 at 11:05
  • @adarsh The database field is filled with `datetime.utcnow` on the python/sqlalchemy level and defined as `DateTime(timezone=True)`. The string I used in the question is the one saved in Postgres. – Michael Mar 30 '15 at 11:09
  • 2
    `2015-03-30 07:19:06.746037+02` should be the actual time in the time zone with offset `+02:00`, not UTC. It seems to be converted already. You shouldn't need to add 2 more hours. – adarsh Mar 30 '15 at 11:11
  • Seems like sqlalchemy/postgres is doing this automatically, I'm not doing any converstions in between. – Michael Mar 30 '15 at 11:17
  • Yep! You could always use `AT TIME ZONE 'whatever time zone'` to get it in that time zone, if you need that. – adarsh Mar 30 '15 at 11:20

3 Answers3

0

In your question the timestamp is already in desired timezone, so you don't need to do anything.

If you want to convert it to some other timezone you should be able to use;

YourModel.datetime_column.op('AT TIME ZONE')('your timezone name')

or,

func.timezone('your timezone name', YourModel.datetime_column)

in SQLAlchemy level.

On python level, consider using pytz

adarsh
  • 6,738
  • 4
  • 30
  • 52
  • I'm somehow coming to the conclusion that the timestamp in my database is wrong, as @Bernhard and you said the timezone is already in there. I did some tests and inserted a date manually into the database with `now()` (postgres sql, not python) and the time came back as expected. If I insert it with python the timestamp is already off 2h. (If it's `3.13pm` right now the database will hold `1.13pm+2h` as the value) Could that be the problem? – Michael Mar 30 '15 at 13:22
  • I would think that the database would think of time passed from python as UTC since the time from python might be naive. And it since the database stores the time with time zone information it returns it in the database's system time. So I think you might need to also include the timezone information while you write to database. Whereas `now()` is a database function which generates time with timezone information. – adarsh Mar 30 '15 at 14:33
0

You don't need to do conversions manually when you use time zone aware database timestamps unless the timezone you want to display is different from the system timezone. When you read and write datetime objects to the database the timezone info of the datetime object is taken into account, this means what you get back is the time in the local time zone, in your case +0200.

Bernhard
  • 8,583
  • 4
  • 41
  • 42
  • Your're right that everything is taken care of on a database and python object level, problem arises when I want to translate this into text. As I said before, I want to show to my frontend users the date/time in their known format. A simple `date.strftime` will output the time information without taking the timezone into account. It will be off 2h even though the information is present in the date object, so I added 2h. I was just not sure if this is a good way to do it. – Michael Mar 30 '15 at 12:28
0

This SO post answers how to get local time from a timezoned timestamp.

Basically, use tzlocal.

import time
from datetime import datetime

import pytz # $ pip install pytz
from tzlocal import get_localzone # $ pip install tzlocal

# get local timezone    
local_tz = get_localzone() 

# test it
# utc_now, now = datetime.utcnow(), datetime.now()
ts = time.time()
utc_now, now = datetime.utcfromtimestamp(ts), datetime.fromtimestamp(ts)

local_now = utc_now.replace(tzinfo=pytz.utc).astimezone(local_tz) # utc -> local
assert local_now.replace(tzinfo=None) == now
Community
  • 1
  • 1
AlexLordThorsen
  • 8,057
  • 5
  • 48
  • 103