How to convert millisecond to date and time using mysql?

by lindsey.homenick , in category: MySQL , 6 months ago

How to convert millisecond to date and time using mysql?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

by elisha_langworth , 6 months ago


You can use the built-in MySQL functions FROM_UNIXTIME and UNIX_TIMESTAMP to convert milliseconds to date and time.

Here is an example:

SELECT FROM_UNIXTIME(<milliseconds> / 1000) AS converted_datetime;

Replace <milliseconds> with the actual value in milliseconds that you want to convert. Divide it by 1000 to convert it to seconds before passing it to FROM_UNIXTIME function.

The FROM_UNIXTIME function converts a Unix timestamp (given in seconds since 1970-01-01 00:00:00 UTC) to a datetime value.

Here's a complete example that converts a millisecond value to a specific date and time format:

SELECT DATE_FORMAT(FROM_UNIXTIME(<milliseconds> / 1000), '%Y-%m-%d %H:%i:%s') AS converted_datetime;

In this example, the DATE_FORMAT function is used to specify a custom date and time format. You can modify the format string according to your requirements. The format %Y-%m-%d %H:%i:%s represents year-month-day hour:minute:second.

Remember to replace <milliseconds> with your actual millisecond value.

Note: MySQL's FROM_UNIXTIME function supports timestamps from 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC. If you have timestamps outside this range, you may need to handle them differently.