how work with unix timestamp on postgresql

Question

I have to work with a postgres database 9.4 and i want to work with timestamp. The first 'problem' is that when i create a timestamp column on postgres, i don't know what it does internally but when i query it returns '2010-10-30 00:00:00'

For me a timestamp is something like this 12569537329 (unix timestamp).

I say that because is a integer or a float, it's way easier for computer to deal comparing to string, and each country has his own time format, with unix timestamp is a number and end of story.

Querying from php the result is a string, so i have to make a bunch juggling and because of time zone, day light saving and other things something might could gone wrong.

I searched a lot of and can't find a way to work with unix timestamp on postgresql.

Can someone explain if there a way, or the right way to work and get as close as possible to unix timestamp.

UPDATE

One thing that i found that it gonna help me and it take a long time to discover that is possible on postgresql is change the Interval Output.

pg manual

In php the date interval for month is 'month' for pg is 'mon' on php it will understand mon as monday. I think that if you have to juggle too much you are doing it wrong. Gladly postgres let us to change that behavior for session or permanently. So setting intervalstyle to iso_8601 it will work as php iso_8601 and will output P1M

Answer 1

Just convert the unix time_t to/from a timestamp, and use that in postgres:

CREATE TABLE omg
        ( seq SERIAL NOT NULL PRIMARY KEY
        , stampthing TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
        );

INSERT INTO omg(stampthing) SELECT now();

INSERT INTO omg(stampthing) VALUES
 ('epoch'::timestamptz )
 , ('epoch'::timestamptz + 12569537329 * '1 second'::interval)
        ;

SELECT stampthing
        , DATE_PART('epoch',stampthing) AS original
FROM omg;

---

Output:

CREATE TABLE
INSERT 0 1
INSERT 0 2
          stampthing           |     original     
-------------------------------+------------------
 2016-01-20 16:08:12.646172+01 | 1453302492.64617
 1970-01-01 01:00:00+01        |                0
 2368-04-24 20:08:49+02        |      12569537329
(3 rows)

Answer 2

If you just query a timestamp column in postgres, you'll get a formatted date. If you prefer the unix timestamp integer you can either cast it when you return it using a syntax like

select extract(epoch from '2011-11-15 00:00:00+01'::timestamp with time zone)::integer;

If you do this a lot, you may make a view with the extract.

or you can define your timestamp column as an integer and store your data using the extract()