Extract date,month,year and month name from the unix timestamp with postgresql

Question

I use postgres for the rails app and I have a unix timestamp in postgresql db. I have a requirement to select and group by the dd-mm-yyyy and by month name.

Consider I have the following unix timestamp

1425148200

and I would need to change this to datetime and I used to_timestamp which returned

2015-02-28 18:30:00 UTC

and I tried to convert the datetime to local timezone using

::timestamp without time zone AT TIME ZONE 'IST'

but that did not give time in required timezone and instead it returned

2015-02-28 16:30:00 UTC

and I tried to get the date part using ::date which returned

Sat, 28 Feb 2015

So please help me get the dd-mm-yyyy in specified timezone and month name(March) from the unix timestamp.

Thanks in Advance!

did not return time in required timezone - what does it mean? what return? — Maxim
– Maxim, Commented Mar 20, 2015 at 10:54
I have updated the result of :timestamp without time zone AT TIME ZONE 'IST' — logesh
– logesh, Commented Mar 20, 2015 at 10:57
Would this: select DATE(to_timestamp(1425148200)) be what you want? This would return 2015-02-28, date in format requested by you. — cristian
– cristian, Commented Mar 20, 2015 at 11:45

Vao Tsun · Accepted Answer · 2015-03-20 13:39:17Z

2

select to_char(to_timestamp('1425148200')::timestamptz at time zone 'UTC-5:30','DD-MM-YYYY & of course Month')

01-03-2015 & of course March

It is postgres mistake I guess according to http://www.postgresql.org/docs/7.2/static/timezones.html enter image description here

edited Mar 20, 2015 at 13:39

answered Mar 20, 2015 at 11:49

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

3 Comments

logesh Over a year ago

The time corresponding to '1425148200' in IST is "3/1/2015, 12:00:00 AM GMT+5:30" but it gives as 2015-02-28 20:30:00 UTC

Vao Tsun Over a year ago

@Logesh there are four time zones with ISTabbreviation. postgres knows Israel one

logesh Over a year ago

Yes, I looked into that and I could somehow solve this with the solution that you gave and I also tried getting the offset and adding to timestamp and that too works. Thanks anyway.

Collectives™ on Stack Overflow

Extract date,month,year and month name from the unix timestamp with postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related