How can I display local time for data in Rails PostgreSQL correctly

Question

And by correctly I mean if the event is Jan. 24 at 9 am Pacific time, I want to see Jan. 24 at 9 am, not Jan. 24 at 5 pm. I'm recording events with time and date in Rails in a PostgreSQL database. Timestamp with time zone (timestampz). All is well and good as long as I keep the database and app on my computer in "US/Pacific", but when I try to use it from the Heroku server which appears to be in UTC, the times are shifted by 8 hours. I now finally get that timestampz only means that this is the datetime in UTC, but the zone isn't stored. Do I need to store the time zone of the even in another field and do the math to see the time and date correctly?

Adding config.time_zone = "America/Los_Angeles" or to "UTC" to config/application.rb gives the same results as before, i.e., this setting made no difference. And this wouldn't be the final solution because the data won't always be in US/Pacific.

Viewing in a table:

<%= stat.statdate.localtime.strftime("%a %d %b %Y") %></td> <td class="text-start"> <%= stat.statdate.localtime.strftime("%_l:%M %P") %>

which results in Thu 27 Jan 2022 9:05 am on localhost, but Thu 27 Jan 2022 5:05 pm on Heroku

And charting the value over time, both over say a year and daily. For plotting variation over a recent day. Quite tortured trying to get both table and charting to work

<% timeZone = last_record.localtime.formatted_offset(false).slice!(0, 3).to_i %>

<% begin_1_day_ago = (last_record-timeZone.hour-1.day).beginning_of_day %> <% begin_2_day_ago = (last_record-timeZone.hour-2.day).beginning_of_day %>

<% span21 = begin_2_day_ago..begin_1_day_ago %>

<%= line_chart BP.where(statdate: span21).pluck(:statdate, :value) %>

Where line_chart is using gem "chartkick" which uses High Charts. But this doesn't work when I move the app and database to Heroku

Answer 1

I ended up doing it "manually." Getting the time zone extracted from the data

<% last_record = BP.order('statdate asc').last.statdate %><br> # last data point. 
<% last_record_zone = BP.order('statdate asc').last.statzone  %> # time zone grabbed from statdate in timestampz (so zone was added)
<% last_record_zone_in_seconds = last_record_zone*3600 %>
# Getting midnight for the day of the last record in UTC but shifted by the local time of the last record
<% last_record_midnight = last_record + last_record_zone_in_seconds - (last_record.min*60) - (last_record.sec) %>

# Setting variables used to title the graphs and set beginning and end of daily graphs (set for one week, not shown)
# The first day is a special case and not shown here
#
# For graph title
<% last_record_1_day_ago = last_record-1.day %>
<% last_record_2_day_ago = last_record-2.day %>
#  For daily graph data
<% span10 = last_record_midnight-1.day..last_record_midnight-0.day %>

Haven't checked for other than localhost and current server.