Include days in date calculation

15 posts by 2 authors in: Forums > CMS Builder
Last Post: January 13, 2015   (RSS)

By Jesus - January 8, 2015

Hi,

I found a post that it uses what I'm looking for, but I need to include days on the formula so, Is it possible to include days on this date calculation? 

Here's a link to the original post (from Deborah): http://www.interactivetools.com/forum/forum-topics-create.php?forumNum=791&postNum=2211635

<?php
$today = time() - strtotime($accountsRecord['hire_date']); 
$yearsOld = intval($today/60/60/24/7/4.35/12); 
$monthsOld = intval($today/60/60/24/7/4.35); ?> 

<?php if ($yearsOld < 1):{echo $monthsOld;} ?> months
<?php elseif ($yearsOld == 1):{echo "1 year";} ?>
<?php else: ?><?php echo $yearsOld ?> years
<?php endif ?> 

Thanks in advance,

Jesus

By claire - January 9, 2015

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

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By Jesus - January 9, 2015

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)

By claire - January 9, 2015

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.

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By Jesus - January 9, 2015

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

Jesus

By Jesus - January 12, 2015

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

By claire - January 12, 2015

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?

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By Jesus - January 12, 2015

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!

By claire - January 13, 2015

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.

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/