How to Retrieve Microseconds or Milliseconds from MySQL Current Time

How to retrieve microseconds or milliseconds from MySQL current time?

MySQL 5.6 supports the millisecond precision in the sysdate function.

try

select sysdate(6) will return 2013-04-16 13:47:56.273434

and

select sysdate(3) will return 2013-04-16 13:47:56.273

CURRENT_TIMESTAMP in milliseconds

To get the Unix timestamp in seconds in MySQL:

select UNIX_TIMESTAMP();

Details: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

Not tested PostgreSQL, but according to this site it should work: http://www.raditha.com/postgres/timestamp.php

select round( date_part( 'epoch', now() ) );

GET milliseconds with DATE_FORMAT in mysql

If you really want use DATE_FORMAT you can do this:

SELECT SUBSTRING(DATE_FORMAT(NOW(3), '%d-%b-%y %H:%m:%s.%f'),1,22);

how to get load time in milliseconds or microseconds in mysql

That time's calculated by the mysql monitor application and isn't done by the mysql server. It's not something you can retrieve programatically by doing (say) select last_query_execution_time() (which would be nice).

You can simulate it in a coarse way by doing the timing in your application, by taking system time before and after calling the query function. Hopefully the client-side overhead would be minimal compared to the mysql portion.

MySQL UNIX_TIMESTAMP(now()) - current time in milliseconds

You need to pass an argument to the NOW() function to specify how many digits of fractional seconds you want it to include. It supports microseconds, but if you want milliseconds you can just use NOW(3) and then multiply the result by 1000, like this:

SELECT UNIX_TIMESTAMP(NOW(3))*1000 as now_in_mili

Timestamp with a millisecond precision: How to save them in MySQL

You need to be at MySQL version 5.6.4 or later to declare columns with fractional-second time datatypes. Not sure you have the right version? Try SELECT NOW(3). If you get an error, you don't have the right version.

For example, DATETIME(3) will give you millisecond resolution in your timestamps, and TIMESTAMP(6) will give you microsecond resolution on a *nix-style timestamp.

Read this: https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html

NOW(3) will give you the present time from your MySQL server's operating system with millisecond precision.

If you have a number of milliseconds since the Unix epoch, try this to get a DATETIME(3) value

FROM_UNIXTIME(ms * 0.001)

Javascript timestamps, for example, are represented in milliseconds since the Unix epoch.

(Notice that MySQL internal fractional arithmetic, like * 0.001, is always handled as IEEE754 double precision floating point, so it's unlikely you'll lose precision before the Sun becomes a white dwarf star.)

If you're using an older version of MySQL and you need subsecond time precision, your best path is to upgrade. Anything else will force you into doing messy workarounds.

If, for some reason you can't upgrade, you could consider using BIGINT or DOUBLE columns to store Javascript timestamps as if they were numbers. FROM_UNIXTIME(col * 0.001) will still work OK. If you need the current time to store in such a column, you could use UNIX_TIMESTAMP() * 1000

Is MySQL now() interval in milliseconds possible?

MySQL's DATE_SUB function (documentation here) accepts MICROSECOND as one of the interval type, so you can subtract 500 * 1000 microseconds and convert it into Timestamp, e.g.:

$stmt = $mysqli->prepare('SELECT chat_user, chat_msg FROM chat_msg 
                          WHERE chat_id = ? 
                          AND chat_time > (NOW() - INTERVAL 500000 MICROSECOND) 
                          AND chat_user != ?');

If chat_time is of Timestamp type, you can use TIMESTAMP(NOW() - INTERVAL 500000 MICROSECOND); or UNIX_TIMESTAMP(NOW() - INTERVAL 500000 MICROSECOND); in the WHERE condition.

Getting MySQL 5.7 to show milliseconds, not microseconds

This was a side effect of using the mycli MySQL command line tool. It does not occur when using standard mysql.

---

Related Topics