0

Context: I have a column of type timestamp without time zone containing timestamps recorded by a datalogger in UTC time. When I import this column in R, the time part is lost (see this SO post on that). However, I cannot use the argument as.is = TRUE as suggested in the answers. However, when the column type is timestamp with time zone, the transfer to R works well. I do not have admin rights on this database and cannot ALTER tables (or I do not know how to do it properly).

Problem: when I convert timestamp without time zone into timestamp with time zone, the timestamp is displayed in my local time zone (here UTC+1).

Question: how to convert a timestamp without time zone in a timestamp with time zone without getting the ouput in local time zone?

What I tried: I got the desired output using SET TIME ZONE 'UTC'; before the query as suggested here but I am wondering if there is a proper solution without having to change the entire time zone of the session as it might have side effects latter on.

Example:

SELECT TIMESTAMP '2022-12-01 11:00:00' -- timestamp without tz, recorded by a datalogger in UTC
AT TIME ZONE 'Etc/Universal' -- timestamp with tz, displayed in local tz, considering timestamp from previous line as UTC

Output:

"2022-12-01 12:00:00+01" (timestamp with time zone)

Expected output:

SET TIME ZONE 'UTC';
SELECT TIMESTAMP '2022-12-01 11:00:00' -- timestamp without tz, recorded by a datalogger in UTC
AT TIME ZONE 'Etc/Universal' -- timestamp with tz, displayed in local tz, considering timestamp from previous line as UTC

"2022-12-01 11:00:00+00" (timestamp with time zone)

I am not familiar with SQL, please do not hesitate to link this post to an existing one with relevant answers, I might have used the wrong keywords.

Paul
  • 2,850
  • 1
  • 12
  • 37
  • 1
    That is impossible. `timestamp with timezone` is *always* formatted according to the current setting of `timezone`. Does R not understand the time zone offset? Perhaps you can report all this as bug in the R driver. – Laurenz Albe Dec 01 '22 at 15:13
  • 1
    Ok, as a quick fix I will use `SELECT to_char(TIMESTAMP '2022-12-01 11:00:00', 'YYYY-MM-DD HH24:MI:SS')` which converts the column to character and then I can use R functions to convert it back to POSIXct and co. – Paul Dec 01 '22 at 15:38

0 Answers0