FROM_UNIX alternate in PostgreSQL

Question

Guys I have stored unix timestamps in my MySQL database, where I was reading them using the MySQL function FROM_UNIX().

Now I'm migrating the database from MySQL to PostgreSQL. In PostgreSQL, how I can read unix timestamps just like I was doing in MySQL with FROM_UNIX() ?

score 8 · Accepted Answer · 2012-08-30 10:50:41Z

8

Taken from the manual:

to_timestamp(double precision) convert Unix epoch to time stamp

If you need parts of the created timestamp, use the extract function

select extract(year from to_timestamp(1284352323))

edited Aug 30, 2012 at 10:50

answered Aug 30, 2012 at 10:15

user330315

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

5 Comments

user1551373 Over a year ago

to_timestamp basically converts unix timestamp to readable PostgreSQL timestamp. I don't need all. I want specific things from unix timestamp. For example I want only the Year from timestamp. In MySQL is done like FROM_UNIXTIME(field,"%YY")

user330315 Over a year ago

@user1551373: then use the extract function to get that from the resulting timestamp.

Craig Ringer Over a year ago

@user1551373 extract is SQL standard by the way, and should work just fine in MySQL too. to_standard doesn't seem to be though it's supported in Oracle and some other databases. You might want to consider a writing a wrapper function if your code needs to be portable.

user330315 Over a year ago

@CraigRinger: what do you mean with to_standard?

Craig Ringer Over a year ago

@a_horse_with_no_name Argh, to_timestamp . Sorry.

baklarz2048 · Accepted Answer · 2012-08-31 08:39:11Z

4

SELECT to_char(date(to_timestamp(1195374767)),'YYYY-MM-DD');

to_timestamp - convert to Postgresql timestamp no unix timestamp
date convert to date type
to_char format output

http://www.postgresql.org/docs/8.1/static/functions-formatting.html

extract can do the same thing but not in one step

edited Aug 31, 2012 at 8:39

answered Aug 30, 2012 at 10:53

baklarz2048

11k2 gold badges33 silver badges38 bronze badges

2 Comments

Craig Ringer Over a year ago

It's worth adding a brief note of explanation about what you're doing and a link to the docs. See @a_horse_with_no_name's answer. Your answer will probably get flagged by the low quality question moderator's system and deleted as a code-only answer with better answers present.

haba713 Over a year ago

You can leave date(...) out. to_char(to_timestamp(1195374767),'YYYY-MM-DD')

Collectives™ on Stack Overflow

FROM_UNIX alternate in PostgreSQL

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related