CODEWITHSUNDEEP
pythonpostgresqldatetimetimestamp
Marvin Oßwald
Marvin Oßwald

Reputation: 407

Convert UTC Timestamp to a normal date & timr

I have a PostgreSql which creates a timestamp with timezone for every row. But now when i try to printout this timestamp I get the following : "2013-03-06 00:00:00+01:00"

Firstly why says it 00:00:00 ?

Secondly how could I convert it to such a thing:

06.03.2013 07:10:13

Or

For Example : "12 sec ago", "1 day and a hour ago"

Thank you Marvin

Upvotes: 1

Views: 1176

Answers (2)

Michał Niklas
Michał Niklas

Reputation: 54322

You got 00:00:00 as time part probably because somebody inserted just date without time part. See for results of:

select current_date, current_timestamp;

you will get:

"2013-03-07";"2013-03-07 08:31:59.098661+01"

When you INSERT or UPDATE timestamp column with just date then time part will be cut to 00:00:00. You should use current_timestamp if you want date , time and time zone.

Converting such date for string can be done on both server and client side. There are many PostgreSQL datetime functions, PostgreSQL format functions that can be used as well as Python functions.

With:

select to_char(current_timestamp, 'DD.MM.YY HH12:MI:SS')

you will get something like:

"07.03.13 08:40:50"

Lafada answer covers Python formating of datetime objects.

When you want to show difference between now and some datetime you can use PostgreSQL age() function:

select age(timestamp '2003-03-01');

result:

"10 years 6 days"

Of course Python also can show difference between timestamps:

import datetime
dt_old = datetime.datetime.strptime('2001-12-31 22:33:44', '%Y-%m-%d %H:%M:%S')
dt_diff = (datetime.datetime.today() - dt_old)
print(dt_diff)

my result:

4083 days, 10:16:14.140000

Upvotes: 1

Neel
Neel

Reputation: 21297

You have to use datetime of the python module. From that module you can convert string to date and format that date.

>>> from datetime import datetime
>>> datetime.strptime('06.03.2013 07:10:13', '%d.%m.%Y %H:%M:%S').strftime('%Y-%d-%m %H:%M:%S%Z')
'2013-06-03 07:10:13'

Upvotes: 1

Related Questions