Extract and reformat PostgreSQL query output (python)

Question

I'm querying a PostgreSQL database with asyncpg. My query is checking a table to determine if a specific record number exist. If the record exist then the date_modified information is extracted from that record, which I need to compare with a newly collected date.

My current issue is trying to extract the timestamp information from the list, which is returned by the query, which is shown below:

[<Record date_modified=datetime.datetime(2010, 9, 9, 8, 33, 31)>]

The only way that I have found to obtain the date string is with a regex.

 date_modified = re.search(r'(\d{4},\s\d{1,2},\s\d{1,2},\s\d{1,2},\s\d{1,2},\s\d{1,2})',  repr(date_check))

 print (date_modified.group(0))

 outputs: 2010, 9, 9, 8, 33, 31

How do I convert the output above to this format?

2010-09-09 08:33:31

Mayank Porwal · Accepted Answer · 2018-11-08 17:47:27Z

2

Try this:

import datetime

In [1465]: datetime.datetime.strftime(date_modified,'%Y-%m-%d %H:%M:%S')
Out[1465]: '2010-09-09 08:33:31'

Read the doc strftime for more info.

answered Nov 8, 2018 at 17:47

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Life is complex Over a year ago

My primary issue is extracting the date_modified information from the returned query.

Mayank Porwal Over a year ago

Does the list has just one element? or more? If it has more, can you please share some? That'll give me a better idea.

Life is complex Over a year ago

The list only has the one element as shown in my original post.

Life is complex Over a year ago

I know that I can do this: re.search(r'(\d{4},\s\d{1,2},\s\d{1,2},\s\d{1,2},\s\d{1,2},\s\d{1,2})', repr(test)), but I was wondering if there is another way.

Mayank Porwal Over a year ago

I was also trying at my end. Looks like too much parsing is required for it.

Life is complex · Accepted Answer · 2018-11-08 19:21:46Z

0

I solved this problem by doing this:

date_modified = re.search(r'(\d{4},\s\d{1,2},\s\d{1,2},\s\d{1,2},\s\d{1,2},\s\d{1,2})',  repr(date_check))
reformattedDate = datetime.datetime.strptime(date_modified.group(0), '%Y, %m, %d, %H, %M, %S').strftime('%Y-%m-%d %H:%M:%S')

answered Nov 8, 2018 at 19:21

Life is complex

15.8k5 gold badges34 silver badges72 bronze badges

1 Comment

Ethan Furman Over a year ago

Is date_check already a datetime.datetime? If so, you don't need the re, just reformattedDate = date_check.strptime('%Y ...') as Mayank suggested.

Collectives™ on Stack Overflow

Extract and reformat PostgreSQL query output (python)

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related