Page 1 of 1

MySQL convert TIMESTAMP to TIME

Posted: Fri Jan 22, 2010 11:53 am
by sn4k3
how can i convert TIMESTAMP (YYYY-MM-DD HH:MM:SS) to TIME (HH:MM:SS)

example:

today is: "2010-01-22 17:00:00"
target is "2010-01-24 17:00:00"

so what i want is: CONVERT_TO_TIME(target-today)
so the disired output will be: '48:00:00'

how could i do that?

thanks

Re: MySQL convert TIMESTAMP to TIME

Posted: Fri Jan 22, 2010 11:58 am
by jayshields
You could use DATE_FORMAT.

Code: Select all

SELECT DATE_FORMAT(`my_date`, '%H:%i:%s') AS `time`
Edit: You edited your post. Anyway, try subtracting the dates. Or converting the dates to unix timestamps and then subtracting them.

Re: MySQL convert TIMESTAMP to TIME

Posted: Fri Jan 22, 2010 12:13 pm
by sn4k3
jayshields wrote:You could use DATE_FORMAT.

Code: Select all

SELECT DATE_FORMAT(`my_date`, '%H:%i:%s') AS `time`
Edit: You edited your post. Anyway, try subtracting the dates. Or converting the dates to unix timestamps and then subtracting them.
dont work

i tried:

Code: Select all

SELECT DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP('2010-01-24 17:00:00')-UNIX_TIMESTAMP('2010-01-22 17:00:00')), '%H:%i:%s')
and returns: 00:00:00

Re: MySQL convert TIMESTAMP to TIME

Posted: Sun Jan 24, 2010 4:17 pm
by VladSun

Re: MySQL convert TIMESTAMP to TIME

Posted: Sun Jan 24, 2010 6:23 pm
by sn4k3
can you read initial post?

i dont want extract time, i want to convert TIMESTAMP to TIME
mysql dont contain such function, maybe i need a php function to do that

example:

now date: 2010-10-05 15:00:00
target date: 2010-10-10 15:00:00

so output time will be: 240:00:00

target-now

2010-10-10 15:00:00
2010-10-05 15:00:00
- (MINUS)
-----------------------------

0000-00-05 00:00:00

so that converted to time will be: 240 hours (240:00:00) [5 days]

Re: MySQL convert TIMESTAMP to TIME

Posted: Sun Jan 24, 2010 6:44 pm
by VladSun
Sorry, my bad.
sn4k3 wrote:mysql dont contain such function, maybe i need a php function to do that
[sql]SELECT timediff('2010-10-10 15:00:00', '2010-10-05 15:00:00')[/sql]
=> 120:00:00

sn4k3 wrote:240 hours (240:00:00) [5 days]
5 days = 5 x 24 = 120 hours.

Re: MySQL convert TIMESTAMP to TIME

Posted: Mon Jan 25, 2010 5:56 am
by sn4k3
VladSun wrote:Sorry, my bad.
sn4k3 wrote:mysql dont contain such function, maybe i need a php function to do that
[sql]SELECT timediff('2010-10-10 15:00:00', '2010-10-05 15:00:00')[/sql]
=> 120:00:00

sn4k3 wrote:240 hours (240:00:00) [5 days]
5 days = 5 x 24 = 120 hours.
very thanks

yes 5 days = 120:00:00, dont know how i get 240, i used google to calculate :P