Convert Postgres interval to PHP DateInterval

Question

I am saving a value into a Postgres10 interval type field.

When I return the field, I get the following: 3 years 10 mons 1 day 02:18:00

The problem I seem to be having, mostly, is that it would also return 1 day 02:18:00 if the duration is not years and months long.

Is there a clean way to convert this value into a PHP DateInterval object?

I figure I can use regex but that seems a little messy to me.

mons? Is that correct? Do you mean the code should account for non standard words? — Andreas
– Andreas, Commented Feb 1, 2019 at 8:53

Nick · Accepted Answer · 2019-02-01 09:38:05Z

1

If you change the interval output style to iso_8601 using

SET intervalstyle = 'iso_8601';

You will get output in the correct format for a call to DateInterval::__construct e.g.

SELECT
 INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';

Output:

interval
P6Y5M4DT3H2M1S

Demo on dbfiddle

answered Feb 1, 2019 at 9:38

Nick

147k23 gold badges67 silver badges106 bronze badges

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

3 Comments

mdonehundy Over a year ago

I am using Symfony with Doctrine. Any advise on how to handle through this?

Nick Over a year ago

@Wildcard27 I don't really know Symfony/Doctrine. But can't you just run a query to set the variable and then run your query on the table with the interval in it?

greg Over a year ago

You can probably use Pomm Interval converter.

Collectives™ on Stack Overflow

Convert Postgres interval to PHP DateInterval

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related