0

I am new to PostgresQL and PHP and am working with a PostgresQL Timestamp object and am trying to find the difference between now and that timestamp to display in terms of years, months, and days. Is there to do this?

Thanks!

Mars J
  • 902
  • 3
  • 15
  • 23

2 Answers2

0
select age(now(), '2010-01-02 12:34:35');

The complete version:

select substring(a from 1 for (position('days' in a) + 3)) 
from (select (age(now(), '2010-01-02 12:34:35'))::text) s(a)
;
       substring        
------------------------
 2 years 4 mons 30 days
(1 row)
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
0

You can just subtract two timestamps, the result is an interval.
To get "now" you can use now() or current_timestamp (among others).

SELECT now() - '2010-02-21 20:11:32';

This will display something like this, though:

830 days 23:00:50.127241

To get a justified representation, use age() or justify_interval()

SELECT justify_interval(now() - '2010-02-21 20:11:32');

Displays the same value in a format like you seem to be after:

2 years 3 mons 20 days 23:01:34.095813

If you want a particular output format use to_char()

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • great. I tried doing SELECT now() - '2010-02-21 20:11:32'; but I get a syntax error: unexpected T_STRING. Any ideas? Sorry I'm so new to this! – Mars J Jun 01 '12 at 19:11
  • @MarsJ: Works for me (PostgreSQL 9.1), and I am positive it works with any other version of PostgreSQL this side of the millennium. "error: unexpected T_STRING" is not a PostgreSQL error message. Looks like PHP. A problem with un-escaped single quotes maybe? – Erwin Brandstetter Jun 01 '12 at 19:21
  • @MarsJ: You might have to cast your string as a timestamp: `SELECT NOW() - '2010-02-21 20:11:32'::TIMESTAMP;` – Jonathan Hall Jun 01 '12 at 19:54
  • @MarsJ: This works without casting (while casting never hurts to avoid ambiguities). It's a PHP syntax error. – Erwin Brandstetter Jun 02 '12 at 15:20