Save PostgreSQL date with timezone

Question

When I execute the following query, It updates the date field with time zone

update person set hiredate='2018-06-18 23:59:59-04:00' where id=5684

When I query the same as:

select * from person where id=5684

I get following value for hiredate: 2018-06-19 09:29:59 (in My timezone +5:30)

But I want to execute a query as

update person set hiredate=now()::date + time '23:59:59-04:00' where id=5684

and run the select query, I get the hiredate as: 2018-06-18 23:59:59

Please help me how I can

are you executing the query and checking the result in the same time zone ? — Dimitar Spasovski
– Dimitar Spasovski, Commented Jun 18, 2018 at 18:16

sticky bit · Accepted Answer · 2018-06-19 00:10:41Z

2

time is short for time without time zone. Try

UPDATE person
       SET hiredate = now()::date + time with time zone '23:59:59-04:00'
       WHERE id = 5684;

answered Jun 19, 2018 at 0:10

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kiran gadhe · Accepted Answer · 2018-06-18 15:17:04Z

-1

I think this is what you want is

update person set hiredate= timestamp with   
time zone now()::date + time '23:59:59-04:00' 
where id=5684;

answered Jun 18, 2018 at 15:17

kiran gadhe

7433 silver badges11 bronze badges

time '23:59:59-04:00' is as same as using time '23:59:59' and that is not what the user probably wants.