Include days in date calculation

Hi there - could you describe exactly what dates you're trying to compare in this case?

Hi Claire, thanks for your answer.

Here's what I'm trying to do (a very similar thing that Deborah mentioned on the original post).

Job Start Date vs Today

and on my result I need to have:

year(s) / month(s) / day(s)

Ex.

John Doe, 1 year, 2 months and 3 days

Joe Smith, 2 years, 1 month and 1 day

John Garza, 3 years and 3 months

etc.

Thanks,

Jesus

PS

I've the report builder plugin so I'll like to generate the report on the backend using this plugin (which I think its possible)

Okay, the thread you've referenced here deals with front end code, so it may not work as is. I'll see about referring this to someone here who's familiar with the report builder plugin.

Thank you Claire. I hope I can receive some kind of help so I can create the report I'm looking for.

Jesus

Hi Claire,

I'm still not using any query as I'm looking for information on how can I create the query. So I got stuck when I saw that post I mentioned and you told me that will not help me for the report builder. So I'm trying to solve this mystery :) and see how can I create it.

As always thanks to you and the team for all the work and support!

Jesus

Okay, I think you'll have to get to grips with some more advanced MySQL queries then. It's actually not too difficult at all!

Take a look at this: http://stackoverflow.com/questions/16871167/mysql-query-extracting-date-group-by-day

The line that's important is this one:

SELECT  FROM_UNIXTIME(date,'%Y-%m-%d') AS perday, 
FROM    data 
GROUP   BY FROM_UNIXTIME(date,'%Y-%m-%d')

The FROM_UNIXTIME is the important function, as it lets you do some useful date manipulation - in this case, extracting the day from each datetime - and then group the records by the result.

Does that help?

Lets see.... I need to have this report.

Employer name, How much time he/she had working since he entered.

Table name: trabajadores

Start date field: $record['fecha_de_inicio']

Employer name: $record['nombre']


SELECT  FROM_UNIXTIME(fecha_de_inicio,'%Y-%m-%d') AS perday, 
FROM    trabajadores 
GROUP   BY FROM_UNIXTIME(fecha_de_inicio,'%Y-%m-%d')



Am I heading on the right direction here?

Thanks!

On second thought, this isn't going to do what you need. Try this:

SELECT  nombre, fecha_de_inicio, DATEDIFF(NOW(), fecha_de_inicio) as days_employed
FROM    trabajadores 
GROUP   BY nombre

This should give you the employer, start date, and number of days of employment.