steampipe query shell munges `date` and `timestamptz` types on output

[danstoner]

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).

1. The date postgres type is year, month, and day only... it does not include time of day.
2. The timestamptz postgres type relies on time zone of 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.

[pskrbasu]

Hey @danstoner, thanks for raising this detailed issue. We will take a look at it and get it sorted. Thanks!

[github-actions[bot]]

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 90 days with no activity.

[github-actions[bot]]

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[runhardr]

commenting to un-stale this

[pskrbasu]

UPDATE: Hey @danstoner, I've been looking into this, and it seems like an issue with pgx - the PostgreSQL Go driver we use.

Related issues - https://github.com/jackc/pgx/issues/2243 https://github.com/jackc/pgx/issues/2117

I am still exploring alternative solutions and will keep this thread updated. Thanks!

[github-actions[bot]]

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[danstoner]

commenting to un-stale this