postgresql - why is now() is not right after saving?

Question

so it's like this, I have a procedure like this :

CREATE OR REPLACE PROCEDURE public.savetrbankd(
    _transcode text,
    _id integer,
    _expensecode text,
    _debetamt numeric,
    _creditamt numeric)
LANGUAGE 'plpgsql'
AS $BODY$
Declare 
BEGIN
If _id=0 Then
    INSERT INTO trbankd(transcode,expensecode,ket,debetamt,creditamt)
    VALUES(_transcode,_expensecode,_ket,_debetamt,_creditamt);
Else
    UPDATE trbankd SET 
        expensecode=_expensecode,
        debetamt=_debetamt,
        creditamt=_creditamt,
        LastUpdatedDate=now()
    WHERE transcode=_transcode and id=_id;
END IF;
END;
$BODY$;

lets pay attention to field "lastupdateddate" here. The type is timestamp without time zone, and default value of now().

so after inserting/updating the records with this procedure, I tried to query the data again. so I got : 2021-07-23 23:48:42.805869

but when I insert/update the records, it should be 24 jul 2021 about 1 pm. so we got the different in timezone about 13-14 hours ?

sure, then when I tried to show timezone, it said it was us/pacific. then I set the timezone to asia/bangkok (which is my real timezone), with code like : set timezone='Asia/Bangkok'. the data wasnt changed. it still said lastupdateddate on 23 jul 2021. Ok, maybe the previous entered data wont change as I used timestamp without time zone type. So I tried to update the data again with the stored procedure, and it still shown 2021-07-24 00:29:23.384443 <= note, that I wasted some time trying to figure out the timezone, so when I updated it the 2nd time here, it's about 1 hour already, and my real time is about 2 pm.

so it's still the same different of about 13-14 hours. how could I make sure that the data inserted and queried to be the same with the server (which happened to be my computer here). as an added note, my setting control panel region is us (english), my location has been set to indonesia (gmt +7), and my current time in my computer is right (like I told above it was 1-2pm).

thx in advance

Answer 1

What you have discovered is that storing timestamp data in a timestamp without time zone field is generally not a good idea, especially when working across time zones. For detailed information on how timestamp without time zone(timestamp) and timestamp with time zone(timestamptz) work with time stamps see here:

https://www.postgresql.org/docs/current/datatype-datetime.html

8.5.1.3. Time Stamps

To illustrate what you are seeing:

select '2021-07-23 23:48:42.805869'::timestamp AT time zone 'US/Pacific';
           timezone            
-------------------------------
 2021-07-23 23:48:42.805869-07


select '2021-07-23 23:48:42.805869'::timestamp AT time zone 'US/Pacific' AT time zone 'Asia/Bangkok';
          timezone          
----------------------------
 2021-07-24 13:48:42.805869

---Current time here(I'm in 'US/Pacific')

test(5432)=# show timezone;
  TimeZone  
------------
 US/Pacific
(1 row)

test(5432)=# select now()::timestamp;
            now             
----------------------------
 2021-07-24 08:57:07.780014

                  
test(5432)=# set timezone = 'Asia/Bangkok';
SET
test(5432)=# select now()::timestamp;
            now             
----------------------------
 2021-07-24 22:56:14.659657

The now() is picking up the time zone set in the server(or reset via the client)and using that to create the timestamp. Since it is being stored to timestamp there is no timezone offset. Your choices for dealing with this are:

1. Leave it as it is, timestamp without time zone field and timezone='US/Pacific'. Do the translation to local time zone as needed.

2. Convert field to timestamp with time zone. Before you do this though you will need to be certain you know what the current values actually represent.