I am trying to convert a date in MySQL
to milliseconds
. This is what I have to get the date:
DATE_ADD(mydate, INTERVAL(1-DAYOFWEEK(mydate)) DAY)
But that returns me like 15/02/2015
and I want to get the milliseconds
of that date.
To convert seconds to milliseconds, you need to multiply the number of seconds by 1000. To convert a Date to milliseconds, you could just call timeIntervalSince1970 and multiply it by 1000 every time.
How to get current timestamp in milliseconds in MySQL? FROM_UNIXTIME(UNIX_TIMESTAMP(CONCAT(DATE(NOW()), ' ', CURTIME(3))); will create a timestamp with milliseconds.
In SQL Server, you can use CONVERT function to convert a DATETIME value to a string with the specified format. In MySQL, you can use DATE_FORMAT function.
UNIX_TIMESTAMP() : This function in MySQL helps to return a Unix timestamp. We can define a Unix timestamp as the number of seconds that have passed since '1970-01-01 00:00:00'UTC. Even if you pass the current date/time or another specified date/time, the function will return a Unix timestamp based on that.
Use the UNIX_TIMESTAMP function.
SELECT (UNIX_TIMESTAMP(mydate)*1000) FROM...
UNIX_TIMESTAMP will get you seconds and you need to multiply by 1000 to get milliseconds.
To convert back, use FROM_UNIXTIME() function.
SELECT FROM_UNIXTIME(date_in_milliseconds/1000) FROM ...
Again, you need to divide by 1000 to get it to seconds before using the function.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With