DATETIME Differences?

Wynder

Haven't seen any, but is there a PHP equivilant to VB's datediff() function? I have two dates in the form of yyyy-mm-dd hh:mm:ss and I want to get the difference between them.

So, if I have a start and a stop time, I can say it took 2 days, 4 hours, 20 minutes, and 35 seconds.

Sxooter

I do all that in my Postgresql database with intervals.

For PHP you have to basically convert the two dates to unix timestamps and do the math youself.

TheIceman5

better still, put them in the database as unix timestamp to begin with, lot less hassels. i wont do it any other way, too much mucking around. when they are in the unix tim format you can add take multiply, to what you like to them to get what ever you are after in one simple line of code and format it once oin the way you want it. if you use the gay format of yyyy-mm-dd hh:mm:ss then you got all sorts of formatting issues, well i find that any way.

Sxooter

While I'll agree that MySQL makes date manipulation difficult, Postgresql is FAR better at it, and you can do things like:

select * from table where datefield>'10 march 2001 12:00:00 am' and datefield less than 12 march 2001 5:30:00 pm'

select '12 March 2001' + '3 days':: interval

kind of stuff. Much more rich environment.

But if you've been stuck with MySQL I can understand why you'd avoid using dates in the database.

Then you can create a constraint (another thing mysql users aren't usually famlliar with) like:

create table invoices (orderdate timestamp, filleddate timestamp check (filleddate > orderdate), shippeddate timestamp check (shippeddate>filleddate));

etc. so that you can't have a nonsensical date of filling the order before it was made etc...

You can even make it so you can't put in a time that isn't in a certain range etc...

Postgresql, by the way, understands date / time in almost any format:

20010301 13:00:00
10/17/2001 12:00 pm
2001-01-12

etc...

Real databases are nice that way. :-)

chriskl

Whatever you do, use the databases timestamp type, NOT unix timestamps. Otherwise, you will end up in a nightmare of timezone problems (mktime, etc. are all bad due to timezone awareness).

Also, you have to code too much intelligence into your app to keep computing the unix timestamp of different dates, which is bad if your app server and db server are in different machines and locations.

Chris

TheIceman5

Originally posted by chriskl
Whatever you do, use the databases timestamp type, NOT unix timestamps. Otherwise, you will end up in a nightmare of timezone problems (mktime, etc. are all bad due to timezone awareness).

Also, you have to code too much intelligence into your app to keep computing the unix timestamp of different dates, which is bad if your app server and db server are in different machines and locations.

Chris

this way has its pros and cons but the cons outweigh the pros for me so i stick with the UNIX timestamp.

Sxooter

Other cool uses of storing date / time in a database using the native db format:

select * from table where extract ('dow' from date)=1; -> selects all dates that fall on mondays.

If you do it a lot:

create index table_mon on table (date) where extract ('dow' from date)=1;

You can do the same type of checks for weeks, hours, minutes, seconds, months, etc...

It's nearly impossible to do the same thing without a lot more coding in PHP and unix timestamps.