CODEWITHSUNDEEP
mysql
user2130951
user2130951

Reputation: 2741

More convenient way to convert to seconds since midnight in mysql

I have this very bulky way of converting from TRADETIME in the format of

'20150513T082207'

But I would like to know if there is a shorter way of doing this conversion to make the code more efficient and clean. Though this yields the correct result. 

select date_format(str_to_date(TRADETIME, '%Y%m%dT%H%i%s'), '%H') * 60*60 + 
date_format(str_to_date(TRADETIME, '%Y%m%dT%H%i%s'), '%i') * 60 +
date_format(str_to_date(TRADETIME, '%Y%m%dT%H%i%s'), '%s') as TRADETIME from NFDATA limit 1

Upvotes: 1

Views: 1269

Answers (1)

mika
mika

Reputation: 1971

You could give it a go to TIME_TO_SEC:

SELECT TIME_TO_SEC(str_to_date(TRADETIME, '%Y%m%dT%H%i%s')) as tradetime 
FROM NFDATA limit 1

Upvotes: 2

Related Questions