I have a column in my postgres table which stores the time at which row is inserted or modified in the database. I need to rely on current_timestamp method However it generates in session timezone and not the database timezone. I checked the documentation and could not find out any way to get the database timezone. If I have database timezone I can do current_timestamp at DBTIMEZONE if DBTIMEZONE is database timezone. Is there any way to get db time zone or to generate current_timestamp in database timezone ?
I have a column in my postgres table which stores the time at which row is inserted or modified in the database. I need to rely on current_timestamp method However it generates in session timezone and not the database timezone. I checked the documentation and could not find out any way to get the database timezone. If I have database timezone I can do current_timestamp at DBTIMEZONE if DBTIMEZONE is database timezone. Is there any way to get db time zone or to generate current_timestamp in database timezone ?
Share Improve this question asked Feb 17 at 15:09 yatharth govilyatharth govil 113 bronze badges 3 |1 Answer
Reset to default 0If you want a specific timezone you can do the following:
select current_timestamp at time zone 'America/Chicago'
Otherwise, if you definitely want to use the database timezone, use Palak's answer.
CURRENT_TIMESTAMP"function" returns timestamptz which is an absolute time. It obviously displays in your client time-zone because that is what the client-zone is for. You can read the TimeZone from pg_settings but you probably don't want to. – Richard Huxton Commented Feb 17 at 15:46timestamp with time zonethen the value is always going to be stored as UTC. 3) How it is displayed to the end user is a client issue. – Adrian Klaver Commented Feb 17 at 16:37