Date conversions

[mtuller]

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.
Thanks.

[Michael Fesser]

..oO(mtuller)

>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,

Why not simply store the date in a real DATETIME field? You can always
use UNIX_TIMESTAMP() if you need it. Then all you need would be to call
ADDTIME() to add both values.

If converting the column is not an option, use FROM_UNIXTIME() before
calling ADDTIME().

HTH
Micha

[Rik]

On Mon, 16 Jul 2007 20:35:11 +0200, mtuller <mi******@gmail.comwrote:

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

[Good Man]

Rik <lu************@hotmail.comwrote in
news:op.tvkoaxivqnv3q9@metallium:

On Mon, 16 Jul 2007 20:35:11 +0200, mtuller <mi******@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'):

whoever uses the code would probably wise to use differently named fields -
'date' and 'time', while they work, should probably be avoided.

http://dev.mysql.com/doc/refman/4.1/...ved-words.html

[Rik]

On Mon, 16 Jul 2007 22:52:23 +0200, Good Man <he***@letsgo.comwrote:

Rik <lu************@hotmail.comwrote in
news:op.tvkoaxivqnv3q9@metallium:

>On Mon, 16 Jul 2007 20:35:11 +0200, mtuller <mi******@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'):

whoever uses the code would probably wise to use differently named
fields -
'date' and 'time', while they work, should probably be avoided.

http://dev.mysql.com/doc/refman/4.1/...ved-words.html

Absolutely right, allthough one get around around reserved words quite
easily, it should be more descriptive. However, the question was not how
to set up the database (which could be better) or name the fields. As I
haven't got a clue what the actual meaning of the data is, I really am not
the one to come up with alternative names :-).

--
Rik Wasmus