pgsql: time

[ciozi137]

• time data string should be converted to UTC
• use "now()" function for current time if we use now() it is difficult to log failed writes

[ciozi137]

Solution for converting timestamp w/timezone:

The UTC == T flag probably does not matter since the ISO8601 format will include the timezone (either Z for 'Zulu'/UTC or explicitly include the offset).

We do need the following additional code during the INSERT (and presumably during a SELECT):

[ciozi137]

• llab_000 -- llab_057 use 'timestamp' for the time column
  • example: 2021-06-30 12:26:57.133; 2024-10-01 08:45:23.527 These times are correct, but my understanding is that they are essentially just strings
• llab_058 -- llab_071 use 'timestamptz' for the time column
  • the vast majority (maybe all) or these values are incorrect
  • for example, most recent timestamp in llab_061 is 2024-10-01 04:48:26.988 -0400. It should be 2024-10-01 08:48:26.988 -0400. In other words the times are inserted w/o timezone and the database treats them as UTC time (but displays them in NY time, which is the timezone of the database`

There are several issues here to deal with:

• [x] INSERT to timestamptz-type column: convert time to ISO8601 and use TIMESTAMP WITH TIMEZONE '2024-10-01 08:48:26.988 -0400', as shown above?
• [x] Does above format work to INSERT into a timestamp-type column?
• [ ] How to convert existing timestamp columns to timestamptz and preserve the correct timezone information (or convert to UTC)
• [ ] How to shift existing timestamptz columns that are wrong by -4 hours?

[ciozi137]

The following code will work for inserting time into hypertables that use either timestamp OR timestamptz (using only Timestamp to UTC8601 UTC DateTime.vi does not work)

surprisingly the TIMESTAMP WITH TIMEZONE isn't necessary?

Ah, that is because it is already in UTC. Probably not a safe assumption and the string should be cast to timestamptz (::timestamptz).

The following is valid for inserting into timestamp or `timestamptz columns:

insert into timestamp_demo (ts, tstz)
values('2024-10-01 12:12:31.420519'::timestamptz, '2024-10-01T16:12:31.420Z'::timestamptz);

[ciozi137]

Updated code to get datatype of an empty column (pg_typeof()) will only work if the column has data inserted

SELECT data_type
FROM information_schema.columns WHERE table_schema = 'public'
AND table_name = 'timestamp_demo' AND column_name = 'ts';

[ciozi137]

Get Column Datatype.vi:

[ciozi137]

Add Get Table Time datatype.vi for convenience: