-3

I've recently updated my database structure and i currently have about 500 records in my database, and inserting a record one by one manually is going to be extremely time consuming.

At the moment i have a table called brunches which has the following columns

brunchid timemonday timetuesday timewednesday timethursday timefriday timesaturday timesunday

Currently each column would have a time that the brunch operates but is in the following format '11 AM - 4 PM'

i've created a new table called schedule which has the following columns

brunchid,
mondaystart,
mondayfinish,
tuesdaystart,
tuesdayfinish,
wednesdaystart,
wednesdayfinish,
thursdaystart,
thursdayfinish,
fridaystart,
fridayfinish,
saturdaystart,
saturdayfinish,
sundaystart,
sundayfinish

and the format of that is now 12:00

i am wondering the best way to get the data from each column and convert it from 11 AM - 4PM and changing that automatically to 11:00 AM - 16:00

and then inserting it into the database table schedule

what is the best way to do this?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Jack Daniels
  • 33
  • 5
  • 1
    Possible duplicate of [How do you convert between 12 hour time and 24 hour time in PHP?](https://stackoverflow.com/questions/8742191/how-do-you-convert-between-12-hour-time-and-24-hour-time-in-php) – Rahul Jun 27 '19 at 12:26
  • 4
    _"to 11:00 AM - 16:00"_ - Don't you mean just _"to 11:00 - 16:00"_? Otherwise you would be mixing 24h and 12h formats, which would be really confusing. – M. Eriksson Jun 27 '19 at 12:28
  • 5
    Storing `11 AM - 4PM` in a single column is a general bad idea, because it means that the start and end times are not normalized. Instead, have separate columns for the two points in the date/time range. – Tim Biegeleisen Jun 27 '19 at 12:31
  • 3
    If you made those fields into TIME datatypes and stored a sensible time you would make so many bits of your life easier in the coming days – RiggsFolly Jun 27 '19 at 12:32

1 Answers1

0

You can do this while inserting into Schedule table

    SELECT STR_TO_DATE( '11:00 PM', '%l:%i %p' )

Output

    23:00:00
Adil Saeed
  • 51
  • 2