extracting year from unix time in PostgreSQL

Question

I have a Log table and this is it's description:

user_id(bigint)
  time (bigint)

My question is, how can I extract the year from the time column in PostgreSQL?

This works in MySQL:

 SELECT l.user_id, year(from_unixtime(l.time)) 
 FROM log l;

Tim Lehner · Accepted Answer · 2013-04-11 12:14:24Z

3

select date_part('year', to_timestamp(1365682413));

to_timestamp is our friend here and has taken a unix time since at least version 8.1.

answered Apr 11, 2013 at 12:14

Tim Lehner

15.3k4 gold badges61 silver badges78 bronze badges

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

Comments

Clodoaldo Neto · Accepted Answer · 2013-04-11 12:10:39Z

1

select extract(year from timestamp 'epoch' + "time" * interval '1 second')
from log;

answered Apr 11, 2013 at 12:10

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Thank you very much! I'm newbie in PostgreSQL, and this worked perfectly