Tag Archives: dates

MySQL: Count the total occupancy rate for a time period (month)

2 comments

Recently I encountered a somewhat interesting MySQL/PHP task: determine occupancy for a reservation system. This entailed determining how many reservation days fall within a particular month. It was not possible to simply determine each reservation length (datediff from/to) and sum them because some reservations started before the month in question and other reservations lasted until the month was over. Therefore, one solution is to get the reservation lengths then compare the start and end of the reservation to the start

Read more programming, Software Tips, Work

Set your own date format in MySQL

Leave a comment

Use DATE_FORMAT in MySQL to create your date strings formatted however you like: SELECT CONCAT(YEAR(res_hotel_booking_bookings.created),’-‘,DATE_FORMAT(res_hotel_booking_bookings.created,’%m’) ,’-‘,DATE_FORMAT(res_hotel_booking_bookings.created,’%d’)) AS creationDate, Count(res_hotel_booking_bookings.id) As reservationsMade FROM res_hotel_booking_bookings WHERE res_hotel_booking_bookings.created >= ‘2013-10-15 00:00:00’ AND res_hotel_booking_bookings.created <= ‘2013-10-17 24:00:00’ GROUP BY YEAR(res_hotel_booking_bookings.created), MONTH(res_hotel_booking_bookings.created), DAY(res_hotel_booking_bookings.created) ORDER BY creationDate DESC There are more concise ways to do this, but this was adapted from an earlier method that used CONCAT so I stuck with that… 🙂

Read more Misc Links, tech tips