Postgres: How do I format an int timestamp as readable date string?

Question

Say I have a column that contains unix timestamps - an int representing the number of seconds since the epoch. They look like this: 1347085827. How do I format this as a human-readable date string in my SELECT query?

culix · Accepted Answer · 2012-09-08 08:23:40Z

43

Postgresql has a handy built-in function for this: to_timestamp(). Just wrap that function around the column you want:

Select a, b, to_timestamp(date_int) FROM t_tablename

edited Sep 8, 2012 at 8:23

user330315

answered Sep 8, 2012 at 7:14

culix

10.7k7 gold badges41 silver badges53 bronze badges

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

2 Comments

Craig Ringer Over a year ago

Then use to_char if you want to output a specific date/time/timestamp format.

Vasilii Suricov Over a year ago

@CraigRinger yep, then read an article "how to convert timestamp to date"). The answer is nearly not correct. it should be to_timestamp(date_int)::date to get 2022-05-26 from 1653592838 or TO_CHAR(to_timestamp(date_int), 'dd-mm-yyyy') for 26-05-2022

Collectives™ on Stack Overflow

Postgres: How do I format an int timestamp as readable date string?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related