SQL Query Filter Result By Time Frame

Hi

I have the following SQL query which gives me the total number of hours from one of my meta fields in time format;

SELECT TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(wp_frm_item_metas.meta_value))),'%H:%i' ) AS total_hours
   FROM wp_frm_item_metas
WHERE 1=1
   AND wp_frm_item_metas.field_id = '103'

If I wanted to extend this further to take account of the date that the hours were worked which is stored under wp_frm_item_metas.meta_value WHERE wp_frm_item_metas.field_id = '84' to only calculate the hours from the above within the last 1 month using something like

DATE_SUB(DATE(NOW()), INTERVAL 1 MONTH)

How can I build that into the query please?

I should add that the relationship here is that both field_id mentioned above share the same item_id as in wp_frm_item_metas.item_id

What ever I try I cannot seem to get it to work frustratingly !!

Thanks in advance

Wayne

  • Hoang Ngo
    • Code Slayer

    Hi @Wayne,

    I hope you are well today.

    I assumed that the created_at is the time your hour record was created. I think you can modify your SQL query to this:

    SELECT TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(wp_frm_item_metas.meta_value))),'%H:%i' ) AS total_hours
       FROM wp_frm_item_metas
    WHERE wp_frm_item_metas.field_id = '103' AND created_at BETWEEN DATE( DATE_SUB( NOW() , INTERVAL 1 DAY ) ) AND DATE ( NOW() );

    If you have any additional issues, please let us know and we'll be happy to help.

    Best regards,
    Hoang

  • Wayne
    • The Incredible Code Injector

    Hey @Hoang Ngo

    The above does not work - I think this is my misunderstanding of the previous question by @Luís the created_at would not be used and should be replaced with field_id=84 and WHERE field_id 139 = worked so that examples such as below with a status of Error are ignored.

    The following entries exist within the table for each entry and are joined by the item_id

    meta_value =13/01/16 field_id=84 Date Of Duty (This is the value to use as the date to check between NOW and this date
    meta_value =Error field_id=139 Status Of Duty - this would be where this is a status of 'Worked'

    A full entry with all fields captured from the same item_id are shown in the screenshot

    Regards

    Wayne

  • Luís
    • Support

    Hi @Wayne,

    I hope had been good today!

    Can you give us access to the database, so we can make some tests?

    Please send it privately through our contact form:

    https://premium.wpmudev.org/contact/

    Select "I have a different question" for your topic - this and the subject line ensure that it gets assigned to me.

    Send in:

    Subject: "Attn: Luis Lopes"
    - cPanel username
    - cPanel password
    - Login url
    - Link back to this thread for reference
    - Any other relevant urls

    Cheers, Luís

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.