Postgres Date and Time formats

Question

What is the data-type for this date type in the postgres?

2020-01-04T16:25:25.000+05:30

if I use with date-time with time zone it will only store 2020-01-04T16:25:25

Can i get some update on this?

This is timestamp with timezone. so use timestampz to store in database. You can refer this for better understanding Click Here — Akhilesh Mishra
– Akhilesh Mishra, Commented Jul 31, 2020 at 10:56
Just use it as the column's type, e.g. create table t (the_column timestamptz); — user330315
– user330315, Commented Jul 31, 2020 at 11:03
I'm able add, time with time zone in the postgres default/local db.. but in the server still getting the same problem.. In the both sides I'm using the query.. (CREATE TABLE public.example4 ( date1 timestamptz, date2 timestamptz )) — manu
– manu, Commented Jul 31, 2020 at 11:45

Adrian Klaver · Accepted Answer · 2020-07-31 15:30:47Z

0

I faced this issue once. What I understood at that time, PostgreSQL is defaulted to UTC. Thats why you will always see +00, and not +5.30. To see that, first you have to set the data type as timestamptz ( time stamp with time zone ) , second - you have to set the timeZone. like SET TIMEZONE='Asia/Kolkata' , something like that.

or else, even though you do:

select TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02' AT TIME ZONE 'IST';

you will get to see only up to 54.

edited Jul 31, 2020 at 15:30

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

answered Jul 31, 2020 at 13:59

sys

3403 silver badges17 bronze badges

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

1 Comment

Adrian Klaver Over a year ago

To be clear the timestamptz type is UTC. timestamp is not. Also you don't have to SET TIMEZONE, Postgres will pick up the one set for your server. Especially in this case as the timestamp has the offset present and setting the TIMEZONE is redundant.

Collectives™ on Stack Overflow

Postgres Date and Time formats

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related