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
