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

Please show exactly what you do to get the result you show. PG actually uses the Unix gettime() function for its timestamps and it has excellent support for time zones and DST. Make your question more specific with examples and you'll get an answer. — Patrick
– Patrick, Commented Jan 20, 2016 at 14:09
Right now it's not a specific question, it's a lot of thing that i encountered. one thing that i have fresh in my memory is this. I have a date on my db, 2015/08/31 23:00:00 but with just that i don't know if is already converted with the timezone, or if someone mess the code and saved with the wrong timezone. timestamp is absolute, with that information in hand i pass the timezone to a php function and know exactly how to handle the information. People tend to like this format '2015/08/31 23:00:00' because "i can look and understand" but i'm more concerned to have a reliable information. — Mateus Silva
– Mateus Silva, Commented Jan 20, 2016 at 14:35
PG has both the timestamp and timestamp with time zone data types. The second is always stored in UTC and can be displayed in any time zone you like, the local time will be correct. The first is in the same time zone as the server. The problem you describe is more of data management at user level, not DB related. — Patrick
– Patrick, Commented Jan 20, 2016 at 14:42
@Patrick yes, I'm trying to fix a mess that another developer made, and try to prevent in future. — Mateus Silva
– Mateus Silva, Commented Jan 21, 2016 at 10:53

wildplasser · Accepted Answer · 2016-01-20 15:09:07Z

4

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)

answered Jan 20, 2016 at 15:09

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

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

2 Comments

Mateus Silva Over a year ago

It's not exactly i want but it will work, I'm not familiar with postgres, my will is save the php timestamp as a integer on the db, but i now that it's not the best practice.

wildplasser Over a year ago

The advantage of a real date/time type is that manipulations on it are relatively easy. Adding a few days, finding out whether a timestamp is within a particular month or year, etc. And all including correction leap days and leap seconds (and timezones). And input/output (when needed) is possible; see my answer.

MortenSickel · Accepted Answer · 2016-01-20 15:10:10Z

1

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()

answered Jan 20, 2016 at 15:10

MortenSickel

2,2095 gold badges27 silver badges48 bronze badges

1 Comment

Mateus Silva Over a year ago

i saw this a lot in searching in google, but when i tried to use with a column i always get an error

Collectives™ on Stack Overflow

how work with unix timestamp on postgresql

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related