CODEWITHSUNDEEP
sqlpostgresqldate
Yerko Antonio
Yerko Antonio

Reputation: 687

extracting year from unix time in PostgreSQL

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;

Upvotes: 1

Views: 2221

Answers (2)

Tim Lehner
Tim Lehner

Reputation: 15251

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.

Upvotes: 3

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

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

Upvotes: 1

Related Questions