In Postgres, how to generate a UTC datetime/timestamp column from a text column with YYYY-MM-DDTHH:M-CodePudding

My table has a "Timestamp" column (text type) with YYYY-MM-DDTHH:MM:SSZ formatted dates. I want to generate a timestampz formatted column with a continuous UTC timestamp but have been unable to do it. I have tried many methods suggested in forums and documentation but I have not been able to get anything to work.

Here is a data example from the table:

select "Timestamp",("Timestamp"::timestamp with time zone) from public.mx_time_well_177168 limit 1

This returns:

"2022-12-10T04:10:02-06:00" (Text) and "2022-12-10 10:10:02 00" (timestamp with time zone)

Here are a few examples of my attempts to generate the new column but they all return:

ERROR: generation expression is not immutable SQL state: 42P17

Attempt 1:

alter table public.mx_time_well_177168 ADD COLUMN "TimestampUTC" timestamp with time zone GENERATED ALWAYS AS ("Timestamp"::timestamp with time zone) STORE

Attempt 2:

alter table public.mx_time_well_177168 ADD COLUMN "TimestampUTC" timestamp with time zone GENERATED ALWAYS AS ("Timestamp"::timestamp AT TIME ZONE 'ETC/UTC') STORED

I am not able to change the data type for the existing "Timestamp" column because of legacy applications that use this database.

Any ideas or suggestion would be greatly appreciated.

CodePudding user response：

Check out this informative answer to a somewhat similar question.

This might do the trick for you:

select "Timestamp",("Timestamp"::timestamp with time zone) AT TIME ZONE 'UTC' from public.mx_time_well_177168 limit 1

Try adding AT TIME ZONE 'UTC' to it.

CodePudding user response：

As long as you know the function is truly immutable, you can just declare it as such. So create a function like:

CREATE FUNCTION str2timestamp(text) RETURNS timestamp with time zone
   IMMUTABLE SET timezone = 'UTC' LANGUAGE sql
RETURN CAST($1 AS timestamp with time zone);

That is safe, because timezone is fixed while the function is running.

Such a function can be used to define a generated column.