How to map a Postgres Rails timestampz array with commas

Question

The goal is to strip the date from the datetime from the data so multiple days can be overlayed using Chartkick. The comma is interpreted by Ruby the break between fields. Rails and Chartkick have no problem presumably because they are 'timestamptz' datatype. Timestamp with Time Zone in PG.

day_one_array = [
[Thu, 28 Dec 2023 00:00:00.000000000 PST -08:00, 0.0],
[Thu, 28 Dec 2023 01:00:00.000000000 PST -08:00, 0.0],
<snip>
[Thu, 28 Dec 2023 21:00:00.000000000 PST -08:00, 0.0], 
[Thu, 28 Dec 2023 22:00:00.000000000 PST -08:00, 0.0], 
[Thu, 28 Dec 2023 23:00:00.000000000 PST -08:00, 0.0]]

puts day_one_array.each { |x| puts x }

parsing_daily_data.rb:2: syntax error, unexpected constant, expecting ']'
  [Thu, 28 Dec 2023 00:00:00.000000000 PST ...

Any suggestions how to handle this with Ruby or more directly in Rails. Working with timestampz data is challenging because the data gets reformatted depending on how it's looked at. In PGAdmin the same data appears as '2022-12-05 08:00:00-08'. And quotes come and go. Trying to convert back and forth from timestamp to strings is very challenging.

Answer 1

Trying to convert back and forth from timestamp to strings is very challenging

In PostgreSQL, it's not; use to_timestamp() with a format mask on its way in: db<>fiddle

create table my_timestamptz (tstz timestamptz);

set timezone='PST8PDT';

insert into my_timestamptz
select to_timestamp('Thu, 28 Dec 2023 22:00:00.000000000 PST -08:00, 0.0',
                    'Dy, DD Mon YYYY HH24:MI:SS.US AAA TZH:TZM, AAA')
returning *;

tstz
2023-12-28 22:00:00-08

Then to_char() on its way out:

set timezone='PST8PDT';
select to_char(tstz,
               'Dy, DD Mon YYYY HH24:MI:SS.US TZ TZH:TZM, 0.0') 
from my_timestamptz;

to_char
Thu, 28 Dec 2023 22:00:00.000000 PST -08:00, 0.0

---

You can tamper with evidence directly:

set timezone='PST8PDT';
select to_char('today'::date + tstz::timetz,
               'Dy, DD Mon YYYY HH24:MI:SS.US TZ TZH:TZM, 0.0') 
from my_timestamptz;

to_char
Thu, 01 Feb 2024 22:00:00.000000 PST -08:00, 0.0

Or in the format pattern:

set timezone='PST8PDT';
select to_char(tstz,
               'Tue, 02 Feb 1999 HH24:MI:SS.US TZ TZH:TZM, 0.0') 
from my_timestamptz;

to_char
Tue, 02 Feb 1999 22:00:00.000000 PST -08:00, 0.0