0

It´s possible to search all fields in all tables that correspond to datetime and show his contents?

For example:

select [from all tables] fields where field_type='datetime'

Expected behavior:

+---------------+--------------+--------------------------+----------+
|  field_name   |  type_field  |           data           |  table   |
+---------------+--------------+--------------------------+----------+
| date_invoice  |  date_time   |  2022-01-02 18:45:09.234 | invoices |
| date_invoice  |  date_time   |  2022-01-12 18:45:09.234 | invoices |
+---------------+--------------+--------------------------+----------+
Legna
  • 460
  • 6
  • 19
  • 1
    The answer is no. – Adrian Klaver Jun 03 '22 at 18:07
  • If you're able to share data from tables, there may be a workaround, but in the current state of your question, no relevant answer can exist for your specific problem. – lemon Jun 03 '22 at 18:17
  • You can probably put something together using `query_to_xml()` (see [here](https://stackoverflow.com/a/72475292) or [here](https://stackoverflow.com/a/58536472)) - but what result do you expect? If you have 100 tables with 1 million rows each, do you expect a result with 100 million rows? What if one table has 5 timestamp columns and others only have 1? How many columns should the result contain? Or do you just want a list of all column names (and their tables) that are defined as `timestamp` or `timestamptz` –  Jun 03 '22 at 18:45
  • You can create a procedure that finds all columns of type `DATE`, then assemble queries on the fly, and then run them. – The Impaler Jun 03 '22 at 18:58
  • Please provide an example of the sample data, and the expected result. – The Impaler Jun 03 '22 at 19:00
  • @TheImpaler done. Im looking for a specific field configuration, but its not documented. – Legna Jun 03 '22 at 21:44
  • @a_horse_with_no_name In this case is a small DB. I search a specific field, but it's not document. So, I now the date, but not the field... : / – Legna Jun 03 '22 at 21:47

1 Answers1

1

If you will divide the task, first get all table names:

SELECT table_name FROM information_schema.tables
where table_type='BASE TABLE'

Then, do a loop (changing table_name below) in any programming language and query:

SELECT *
  FROM information_schema.columns
  where table_name   = 'workers'
and data_type='timestamp without time zone'
noszone
  • 172
  • 9