postgresql_query: fix datetime.timedelta type handling (#73520)

3 files changed, 40 insertions, 0 deletions

diff --git a/changelogs/fragments/51-postgresql_query_fix_datetime_timedelta_type_handling.yml b/changelogs/fragments/51-postgresql_query_fix_datetime_timedelta_type_handling.yml
new file mode 100644
index 0000000000..4fef34b41f
--- /dev/null
+++ b/changelogs/fragments/51-postgresql_query_fix_datetime_timedelta_type_handling.yml
@@ -0,0 +1,2 @@
+bugfixes:
+- postgresql_query - fix datetime.timedelta type handling (https://github.com/ansible-collections/community.postgresql/issues/47).
diff --git a/lib/ansible/modules/database/postgresql/postgresql_query.py b/lib/ansible/modules/database/postgresql/postgresql_query.py
index dc386d6a62..ca056566b0 100644
--- a/lib/ansible/modules/database/postgresql/postgresql_query.py
+++ b/lib/ansible/modules/database/postgresql/postgresql_query.py
@@ -168,6 +168,7 @@ rowcount:
     sample: 5
 '''
 
+import datetime
 import decimal
 
 try:
@@ -312,6 +313,9 @@ def main():
                 if isinstance(val, decimal.Decimal):
                     row[key] = float(val)
 
+                elif isinstance(val, datetime.timedelta):
+                    row[key] = str(val)
+
             query_result.append(row)
     except Psycopg2ProgrammingError as e:
         if to_native(e) == 'no results to fetch':
diff --git a/test/integration/targets/postgresql/tasks/postgresql_query.yml b/test/integration/targets/postgresql/tasks/postgresql_query.yml
index 4b7642ee92..52ca350615 100644
--- a/test/integration/targets/postgresql/tasks/postgresql_query.yml
+++ b/test/integration/targets/postgresql/tasks/postgresql_query.yml
@@ -499,3 +499,37 @@
 - assert:
     that:
     - result.rowcount == 1
+
+#############################################################################
+# Issue https://github.com/ansible-collections/community.postgresql/issues/47
+- name: Get datetime.timedelta value
+  become_user: '{{ pg_user }}'
+  become: true
+  postgresql_query:
+    login_user: '{{ pg_user }}'
+    db: postgres
+    query: "SELECT EXTRACT(epoch from make_interval(secs => 3))"
+  register: result
+  when: postgres_version_resp.stdout is version('10', '>=')
+
+- assert:
+    that:
+    - result.rowcount == 1
+    - result.query_result[0]["date_part"] == 3
+  when: postgres_version_resp.stdout is version('10', '>=')
+
+- name: Get interval value
+  become_user: '{{ pg_user }}'
+  become: true
+  postgresql_query:
+    login_user: '{{ pg_user }}'
+    db: postgres
+    query: "SELECT make_interval(secs => 3)"
+  register: result
+  when: postgres_version_resp.stdout is version('10', '>=')
+
+- assert:
+    that:
+    - result.rowcount == 1
+    - result.query_result[0]["make_interval"] == "0:00:03"
+  when: postgres_version_resp.stdout is version('10', '>=')