steampipe query shell munges date and timestamptz types on output #4450
Description
Describe the bug
The steampipe query shell has different behavior than native postgres client(s) when interacting with date and timestamptz (timestamp with time zone) data types.
When connecting to a steampipe database via psql I get the expected results (the same as every other postgres database).
- The
datepostgres type is year, month, and day only... it does not include time of day. - The
timestamptzpostgres type relies ontime zoneof the current session to determine which timezone should be presented. It does not force the output timezone to match my local computer's timezone. It is possible to get results out of postgres that display the timestamp with time zone in UTC.
When using the steampipe interactive query shell, there appears to be "munging" of date and timestamptz data types that is inconsistent with postgres native handling of those types.
Steampipe version (steampipe -v)
Steampipe v1.0.0
To reproduce
Note: laptop local time is UTC-5
$ steampipe query
Welcome to Steampipe v1.0.0
For more information, type .help
> show time zone;
+----------+
| TimeZone |
+----------+
| UTC |
+----------+
> select '1984-01-01T00:00:00-00:00'::date;
+---------------------+
| date |
+---------------------+
| 1984-01-01 00:00:00 |
+---------------------+
> select '1984-01-01T00:00:00-00:00'::timestamptz;
+---------------------------+
| timestamptz |
+---------------------------+
| 1983-12-31T19:00:00-05:00 |
+---------------------------+
> set time zone 'US/Pacific';
> show time zone;
+------------+
| TimeZone |
+------------+
| US/Pacific |
+------------+
> select '1984-01-01T00:00:00-00:00'::timestamptz;
+---------------------------+
| timestamptz |
+---------------------------+
| 1983-12-31T19:00:00-05:00 |
+---------------------------+
Expected behavior
Note: laptop local time is UTC-5
$ steampipe service start
$ psql postgres://steampipe:.../steampipe
steampipe=> show time zone;
TimeZone
----------
UTC
(1 row)
steampipe=> select '1984-01-01T00:00:00-00:00'::date;
date
------------
1984-01-01
(1 row)
steampipe=> select '1984-01-01T00:00:00-00:00'::timestamptz;
timestamptz
------------------------
1984-01-01 00:00:00+00
(1 row)
steampipe=> set time zone 'US/Pacific';
SET
steampipe=> show time zone;
TimeZone
------------
US/Pacific
(1 row)
steampipe=> select '1984-01-01T00:00:00-00:00'::timestamptz;
timestamptz
------------------------
1983-12-31 16:00:00-08
(1 row)
Additional context
It seems like Steampipe output should be able to give me timestamps in the timezone of my choice (or always UTC), but not force timestamps to match my local computer's timezone. Not providing UTC output makes it challenging to match up the output with other tools that are in UTC. Also, when sharing the output / report with collaborators in multiple different timezones we use "Everything is in UTC" for sanity.