This requires understanding first. I wrote a comprehensive answer about how PostgreSQL handles timestamps and time zones here:
Ignoring timezones altogether in Rails and PostgreSQL
You cannot "not" have a time zone. You can operate with the type timestamp [without time zone]
, but you'd still have a time zone in your client.
Your statement:
When I inserted a row with timestamp without time zone (!) using the CURRENT_TIMESTAMP function ...
is a contradictio in adjecto. CURRENT_TIMESTAMP
returns a timestamp with time zone
(!). If you just cast it (or have it coerced automatically) into timestamp
, the time zone offset is truncated instead of applied. Consider:
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
,CURRENT_TIMESTAMP::timestamp
Unless your local time zone setting is ' UTC' or something like 'London', the two expressions return different values.
If you want to save the literal value you see in your time zone, use one of:
SELECT CURRENT_TIMESTAMP::timestamp
,now()::timestamp
,LOCALTIMESTAMP;
If you want to save the point in time as it would be represented in UTC, use one of:
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
,now() AT TIME ZONE 'UTC;