timestamp on postgreSQL to unix timestamp

Question

I am trying to use FLOT chart to plot values that i have stored in PostgreSQL. One of the axes will have timestamps. The format that i have stored it the database is: 2013-11-01 00:05:57 (year-month-day hour:minute:second).

I tried the following code to convert this format to UNIX:

$postgresql_timestamp= 2013-11-01 00:05:57;
$unix_timestamp= strtotime($postgresql_timestamp);

the result that o get is 1383260757 wich corresponds to another date: 2013-10-01 23:05:5

I think that the problem that I am having is related with the timestamp format on PostgreSQL.

It's a timezone problem, your dates are different by ONE hour — Marc B
– Marc B, Commented Nov 21, 2013 at 15:26

Erwin Brandstetter · Accepted Answer · 2013-11-21 22:46:56Z

6

PostgreSQL supports standard SQL's extract() function, which can convert SQL timestamps to Unix timestamps.

select extract(epoch from timestamp '2013-11-01 00:05:57');

edited Nov 21, 2013 at 22:46

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

answered Nov 21, 2013 at 15:24

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

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

3 Comments

bma Over a year ago

Can you supply a link to such a function? I don't recall seeing that in the docs (and a quick look there didn't turn it up).

Mike Sherrill 'Cat Recall' Over a year ago

@bma: I somehow missed your comment.Sorry about that. But extract() is one of PostgreSQL's Date/Time functions. It's been supported (and documented) since at least PostgreSQL 7.1.

bma Over a year ago

If I was referring to extract(), I have no idea why I'd ask that. I've been using that function since the mid 2000's. A mystery lost in the depths of time...

Alexander Farber · Accepted Answer · 2016-02-08 20:35:24Z

1

Since I have access to DB with PHP, I used the following code and get good results:

date_default_timezone_set('UTC');

$unix_timstamp = strtotime($postgreSQL_timestamp);

edited Feb 8, 2016 at 20:35

Alexander Farber

23.4k79 gold badges263 silver badges449 bronze badges

answered Nov 22, 2013 at 10:38

JotaTRod

912 silver badges7 bronze badges

1 Comment

Sprinterfreak Over a year ago

Use extract in your pg_fetch_all(pg_query($handle, "SELECT extract(...) AS epoch, value FROM table;")); gives well calculated epoch timestamps as virtual column 'epoch'. Iterate threw big arrays using PHP to parse timestamps can be slow and inefficient.

Collectives™ on Stack Overflow

timestamp on postgreSQL to unix timestamp

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related