Posted by Rik on 07/16/07 19:26

On Mon, 16 Jul 2007 20:35:11 +0200, mtuller <mituller@gmail.com> wrote:

> I have a database that stores time in 2 different formats. I have a
> unix timestamp, and the other is a representation of time in the
> format 03:00:00 which would represent 3 hrs. I want to convert the 3
> hrs so that I can add it to the unix timestamp. In other words,
>
> The unix timestamp of 1183553400 is equal to Wed, Jul 04, 2007 7:50
> AM. I want to add the 3hrs to be able to display in this format
>
> Wed, July 04, 2007 7:50 AM - 10:50 AM.
>

You made it somewhat difficult for yourself.
Here is how it would be done in MySQL alone (untested, fields named 'date'
and 'time'):

SELECT
CONCAT(
DATE_FORMAT(
FROM_UNIXTIME(`date`),
'%a, %M %D, %Y %r'
),
' - ',
DATE_FORMAT(
DATE_ADD(
FROM_UNIXTIME(`date`), INTERVAL TIME_TO_SEC(`time`) SECOND
),
'%r'
)
) as 'formatted_date'
FROM `tablename`;

Which will still give you grief if it crosses the 24:00:00/00:00:00
threshold.
--
Rik Wasmus

[Back to original message]