Okay
  Public Ticket #1538711
Time Format
Closed

Comments

  •  1
    kindredx started the conversation

    I need to know if this is possible or not.

     

    I have a Phone Time Column where I need to put a duration of time. For example, my employee was on the phone for 01:20:30 (1 Hour, 20 Minutes, and 30 Seconds). I can easily add that information into a time column using wpDataTables, minus the seconds. However, if I want to add the total time for the week, say 40:35:00 I cannot because it goes past the 24 Hours constraint.

     

    In Excel if a field is formatted as Time, one of the format options is to use (what I call) large time, meaning more than 24 Hours in the hours part, hence I can put 40:35:00 into an Excel spreadsheet, but I cannot into a MySQL Time Column?

     

    Is there any way to enter a time in the hhh:mm:ss format?

     

    As a side note, I currently capture the time duration in Minutes, then on a MySQL Query I perform the following:

     

    SEC_TO_TIME(SUM(phonetime*60))

     

    This provides an output in the following format:

     

    33:15:00

     

    Which is exactly what I need for the report. I would like to enter it just as I see it so when I look at single records I know the duration of time for that single record. For now, my only solution is to convert the time I get from my actual phone system into minutes by way of a converter and then plug those minutes into my phone time column.

    Lastly, I would like to say awesome work on wpDataTables, it works awesome!

  •  1
    kindredx replied

    I would like to add that I just realized I have a limitation even with the way I am doing it, and that is once I convert the minutes to Time format I cannot go beyond 839:59:59.

     

    This is really disappointing, is there any way I can do these calculations in a query based on the integer while providing output that is beyond 839:59:59?

  •  1,667
    Miloš replied

    Hi kindredx,
    Thank you for your purchase.

    Unfortunately we do not have seconds in our plugin built in features but I can give you some workaround.

    Only workaround what Datetime with seconds  would work is to have MySQL table with data. Times will be displayed with seconds and sorting will work but editing from WP backed is not possible. You will have to edit this time values directly in database.

    Steps you need to follow to achieve this:

    Have to go in database, find table wp_options. In this table find wdtTimeFormatin option_name column and set h:i:s as option_value for this column.

    After that you have to create new MySQL table where you will place data. This table will be called "datetime_table" in this example, and one of the column (or more then one it depends how much datetime columns do you need) will be DATETIME type:

    Then when you created a table in database, you'll have to create wpDataTable based on datetime_table from database, and you can create it if you choose Add From Data Source and select MySQL table. Your query should be:

    SELECT * FROM datetime_table

    Like that you'll show datetime with second and will be able to sort it, but only way to edit data is directly from the database. 

    Column type for this column should be Datetime in optional column setup.

    Hope this can help you out.

    Best regards.

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | InstagramFront-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | InstagramAmelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables

  •  1
    kindredx replied

    Thanks for the reply Bogdan,

    While your solution may work, I then lose the ability for other individuals to be able to edit the data from the front-end, and that I really need.

    I have been playing around with this and I think I may have an easy solution.

    Currently I enter the time into the field in minutes, basically just an integer, then perform the query sum calculation of:

     

    SEC_TO_TIME(SUM(phonetime*60))

     

    Rather than using the SEC_TO_TIME, I changed it to just perform the following:

     

    SUM(phonetime/60)

     

    This provides output in hours. For example, if the column sums up to say 73000 minutes, the calculation spits out 1216.67 hours, which is 1216 Hours and 40 Minutes. I can deal with this because it will only be used for Quarterly and Yearly reporting on the time.

    I’m ok with displaying it as hours in this fashion, plus I don’t lose the ability to edit data on the front-end. 

     

     

  •  1,667
    Miloš replied

    Hi kindredx,

    Glad to heart that you found a solution, good job.

    As I said seconds are not supported yet in our plugin and we are looking to add this for our next updates of the plugin which I hope that it will be soon.
    Also all of other features of the column settings and table settings will be working with the seconds feature.

    Best regards.

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | InstagramFront-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | InstagramAmelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables