MySQL convert TIMESTAMP to TIME

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sn4k3
Forum Commoner
Posts: 37
Joined: Tue Oct 16, 2007 3:51 pm

MySQL convert TIMESTAMP to TIME

Post 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
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: MySQL convert TIMESTAMP to TIME

Post 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.
sn4k3
Forum Commoner
Posts: 37
Joined: Tue Oct 16, 2007 3:51 pm

Re: MySQL convert TIMESTAMP to TIME

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL convert TIMESTAMP to TIME

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
sn4k3
Forum Commoner
Posts: 37
Joined: Tue Oct 16, 2007 3:51 pm

Re: MySQL convert TIMESTAMP to TIME

Post 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]
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL convert TIMESTAMP to TIME

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
sn4k3
Forum Commoner
Posts: 37
Joined: Tue Oct 16, 2007 3:51 pm

Re: MySQL convert TIMESTAMP to TIME

Post 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
Post Reply