MYSQL - Creating a field for the difference between two times in phpmyadmin

La_Parka

How can I create a functioning field in phpmyadmin that gives the difference in days between 2 dates?

Note: Each of the 2 dates are in other fields in the table.

jburfield

You need to do this with your query. Assuming you use MySQL, do this:

select to_days(datefield1) - to_days(datefield2) from TABLE ...

As long as the two fields are dates this works great.

-- Jason

La_Parka

Originally posted by jburfield
You need to do this with your query. Assuming you use MySQL, do this:

select to_days(datefield1) - to_days(datefield2) from TABLE ...

As long as the two fields are dates this works great.

-- Jason

Does that command mandate a new field in the MYSQL database?

jburfield

In the example I provided, it is NOT a new field in the table, it calculates it on the fly from the two existing fields.

Two benefits: you do not need to store the value, and, it's always up to date when the query runs/page loads.

-- Jason

jeffwhite

I agree it's better to calculate stuff on the fly as opposed to storing it. What happens when one of the values changes? The sum is wrong or has to be calculated and stored again. Yuck!

La_Parka

And which MySQL field type would you suggest for the dates? I tried using "TIME" but it puts it in a 0000-00-00 format, and I would like it in a 00-00-0000 notation.

jeffwhite

Use DATE type
MySQL will store the dates in the 0000-00-00 format.
What I do is use the date() and strtotime() function to format the date people see on the form and then change it back right before I perform the INSERT or UPDATE.
From db to user:
date("m"/"d"/"y",strtotime(datefromdb))
before UPDATE or INSERT
date("Y"-"m"-"d",strtotime(DateUserSees))

Strtotime() is very powerful. I've got users inputting stuff like 'next month' and 'last week'. Strtotime figures it out.

jburfield

You can also use DATETIME fields as well. That is usually what I do, that way I have the time associated with the date as well.

DATETIME or DATE, as jeffwhite suggested, will both work equally well.

-- Jason

La_Parka

Originally posted by jeffwhite
Use DATE type
MySQL will store the dates in the 0000-00-00 format.
What I do is use the date() and strtotime() function to format the date people see on the form and then change it back right before I perform the INSERT or UPDATE.
From db to user:
date("m"/"d"/"y",strtotime(datefromdb))
before UPDATE or INSERT
date("Y"-"m"-"d",strtotime(DateUserSees))

Strtotime() is very powerful. I've got users inputting stuff like 'next month' and 'last week'. Strtotime figures it out.

Would I put the DATE field string in the "datefromdb" area?

jeffwhite

I was trying to illustrate formatting dates for the intended audience whther it be mySQL which must have a 0000-00-00 format or a user who may want a dd-MMM-yy format.

The 'datefromdb' function parameter I used in my example should be replaced with whatever variable contains the date data as it was retreived from mySQL after a select statement.

The 'DateUserSees' should be replaced with whatever variable contains the date data as it came from the client. maybe from a form.

jeff

La_Parka

Originally posted by jeffwhite
I was trying to illustrate formatting dates for the intended audience whther it be mySQL which must have a 0000-00-00 format or a user who may want a dd-MMM-yy format.

The 'datefromdb' function parameter I used in my example should be replaced with whatever variable contains the date data as it was retreived from mySQL after a select statement.

The 'DateUserSees' should be replaced with whatever variable contains the date data as it came from the client. maybe from a form.

jeff

I would like to have both date fields stored in the db strictly by the admin, with no form or any user-input involved in the process. Would I be able to have a table such as this using this method:


   DATE SENT        DATE RECEIVED       TIME IN BETWEEN
     1/1/02         1/6/02               5 days

jeffwhite

Sure