current community

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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 pstgresql. Do you have any advice in order to get a good result?

Thanks

share|improve this question
 
It's a timezone problem, your dates are different by ONE hour –  Marc B Nov 21 '13 at 15:26
add comment

2 Answers

up vote 3 down vote

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');
share|improve this answer
1  
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). –  bma Nov 21 '13 at 15:33
 
No, I'd forgotten that I'd written unix_timestamp() as a wrapper function for extract(). It's not a native function. –  Mike Sherrill 'Cat Recall' Nov 21 '13 at 15:37
add comment
up vote 0 down vote

i found out another possibility: Since i access to DB with PHP i used the follwing code and i get good results:

date_default_timezone_set('UTC');

$unix_timstamp = strtotime($postgreSQL_timestamp);

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.