1

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?

manu
  • 161
  • 7
  • This is timestamp with timezone. so use `timestampz` to store in database. You can refer this for better understanding [Click Here](https://www.postgresqltutorial.com/postgresql-timestamp) – Akhilesh Mishra Jul 31 '20 at 10:56
  • Yes.. but I'm not getting that option.. – manu Jul 31 '20 at 10:58
  • Just use it as the column's type, e.g. `create table t (the_column timestamptz)`; –  Jul 31 '20 at 11:03
  • See the [example](https://www.db-fiddle.com/f/Vyg9QTX2vTpAWwVjA58CH/2) – Akhilesh Mishra Jul 31 '20 at 11:06
  • 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 Jul 31 '20 at 11:45
  • Is the problem that you do not see the milliseconds (`000`) part of the `timestamptz`? If so, then that is simply a formatting problem. https://stackoverflow.com/a/62938325/13808319 If your problem is that you are not seeing the TZ part of the value, then that is probably because you, your server, or both are in the IST time zone. – Mike Organek Jul 31 '20 at 13:03
  • Please show the actual actions/queries you are using to get the values you are reporting. – Adrian Klaver Jul 31 '20 at 15:17

1 Answers1

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.

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
sys
  • 330
  • 3
  • 15
  • 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. – Adrian Klaver Jul 31 '20 at 15:30