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
MySQL convert TIMESTAMP to TIME
Moderator: General Moderators
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
Re: MySQL convert TIMESTAMP to TIME
You could use DATE_FORMAT.
Edit: You edited your post. Anyway, try subtracting the dates. Or converting the dates to unix timestamps and then subtracting them.
Code: Select all
SELECT DATE_FORMAT(`my_date`, '%H:%i:%s') AS `time`Re: MySQL convert TIMESTAMP to TIME
dont workjayshields wrote:You could use DATE_FORMAT.
Edit: You edited your post. Anyway, try subtracting the dates. Or converting the dates to unix timestamps and then subtracting them.Code: Select all
SELECT DATE_FORMAT(`my_date`, '%H:%i:%s') AS `time`
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')Re: MySQL convert TIMESTAMP to TIME
There are 10 types of people in this world, those who understand binary and those who don't
Re: MySQL convert TIMESTAMP to TIME
can you read initial post?VladSun wrote:http://dev.mysql.com/doc/refman/5.1/en/ ... on_extract
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
Sorry, my bad.
=> 120:00:00
[sql]SELECT timediff('2010-10-10 15:00:00', '2010-10-05 15:00:00')[/sql]sn4k3 wrote:mysql dont contain such function, maybe i need a php function to do that
=> 120:00:00
5 days = 5 x 24 = 120 hours.sn4k3 wrote:240 hours (240:00:00) [5 days]
There are 10 types of people in this world, those who understand binary and those who don't
Re: MySQL convert TIMESTAMP to TIME
very thanksVladSun wrote:Sorry, my bad.
[sql]SELECT timediff('2010-10-10 15:00:00', '2010-10-05 15:00:00')[/sql]sn4k3 wrote:mysql dont contain such function, maybe i need a php function to do that
=> 120:00:00
5 days = 5 x 24 = 120 hours.sn4k3 wrote:240 hours (240:00:00) [5 days]
yes 5 days = 120:00:00, dont know how i get 240, i used google to calculate