MySql Start time + duration = endtime

Posted on 2012-08-20
Last Modified: 2012-08-20
Hi there
I have the following problem. A database that has three columns, and the values ¿¿are saved in the format
cal_date -> 20121114
cal_time -> 123000 (gsm, need to add one hour)
cal_duration -> 90

SELECT date (cal_date) AS date, -> 2012-11-14 - ok
time_format (cal_time + 10000, '% H:% i') AS start_time -> 13:30 added one hour ok

The question is how I add cal_duration 90 minutes to cal_time so I get end_time 15:00

thanks in advance
Question by:HelgeLind
    LVL 25

    Accepted Solution

    have a look at:
    SELECT date (cal_date) AS date,
           time_format(cal_time + 10000, '%H:%i') AS start_time,
           DATE_ADD(STR_TO_DATE(concat(cal_date,cal_time),'%Y%m%d%H%i%s'), interval 60 minute),
           DATE_ADD(STR_TO_DATE(concat(cal_date,cal_time),'%Y%m%d%H%i%s'), interval 60+duration minute)
    from (select 20121114 as cal_date, 123000 as cal_time, 90 as duration union all
          select 20121114 as cal_date, 233000 as cal_time, 187 as duration)v

    Open in new window


    Author Closing Comment

    Thanks, lot of timesavning for me :)
    LVL 25

    Expert Comment

    are your cal_time values numeric or string?  If they do not have leading 0 for times before midday - you may want to do:

    Author Comment

    Quite true, I saw it
    80000 - 8 am is not working
    110000 - 11 am is working
    different lengths of strings

    TIME_FORMAT(DATE_ADD(STR_TO_DATE(concat(cal_date,right(concat('00',cal_time),6)),'%Y%m%d%H%i%s'), interval 60 minute ), '%H:%i') as start_time,
    TIME_FORMAT(DATE_ADD(STR_TO_DATE(concat(cal_date,right(concat('00',cal_time),6)),'%Y%m%d%H%i%s'), interval 60+cal_duration minute ), '%H:%i') as end_time

    now i works :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API ( …
    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now