How to get current timestamp with milliseconds in MySQL?

Question

I'm able to get approximate time without milliseconds by query

SELECT UNIX_TIMESTAMP(NOW());

but how to get precise current timestamp with milliseconds? For example, after executing the above query I get 1352717640 value, but I want to get 1352717640xxx value.

UPD

And other answers from SO (like this) offers solution like

SELECT UNIX_TIMESTAMP(NOW(3))*1000;

but this query only fills up milliseconds place with '000', but I want to get PRECISE value of milliseconds, like 1352717640937.

In SQLyog:

And in mysql console:

Is it possible at all for MySQL 5.1?

Possible duplicate of [MySQL 5.6 DATETIME doesn't accept milliseconds/microseconds](https://stackoverflow.com/questions/13344994/mysql-5-6-datetime-doesnt-accept-milliseconds-microseconds) — McNets, Feb 28 '18 at 13:32
@McNets, but in this answer milliseconds only replaced with '000', but I want to get precise value of milliseconds. — Ksenia, Feb 28 '18 at 13:43
Possible duplicate of [mysql UNIX\_TIMESTAMP(now()) - current time in miliseconds](https://stackoverflow.com/questions/48098185/mysql-unix-timestampnow-current-time-in-miliseconds) — Jorge Campos, Feb 28 '18 at 13:45

score 5 · Answer 1 · answered Feb 28 '18 at 14:18

5

I think you didn't test it, but yes the solution is

SELECT UNIX_TIMESTAMP(NOW(3))*1000;

SQL Fiddle

MySQL 5.6 Schema Setup:

Query 1:

SELECT UNIX_TIMESTAMP(NOW(3))*1000

Results:

| UNIX_TIMESTAMP(NOW(3))*1000 |
|-----------------------------|
|               1519827493419 |

answered Feb 28 '18 at 14:18

Daniel E.

2,440
1
14
24

Maybe the reason is MySQL version, because I tested it, and I always got '000' at the end. My version is 5.1... – Ksenia Feb 28 '18 at 18:16
Are you sure you are doing NOW(3) ? – Daniel E. Mar 01 '18 at 08:44
It was executed in SQLyog. – Ksenia Mar 01 '18 at 09:48
And I had a thought, that it could be the bug of SQLyog, but in console I have the same output: http://joxi.ru/82QDYBqIjnDGqm – Ksenia Mar 01 '18 at 09:55
Can you try NOW() and NOW(3), I think it's a bug in your Mysql version – Daniel E. Mar 01 '18 at 09:59
Both of these functions return the same result ('2018-03-01 15:13:28'). – Ksenia Mar 01 '18 at 12:14
I am out of ideas, maybe you can ask a question how to do NOW(3) in mysql 5.1 – Daniel E. Mar 01 '18 at 13:42

score 1 · Answer 2 · answered Dec 30 '22 at 21:16

1

Mysql 8.0.26

SELECT UNIX_TIMESTAMP(NOW(3))*1000;
-- return float 1672434492831.000

To integer:

SELECT FLOOR(UNIX_TIMESTAMP(NOW(3))*1000);
-- return INT 1672434492831

answered Dec 30 '22 at 21:16

MTK

3,300
2
33
49

How to get current timestamp with milliseconds in MySQL?

2 Answers2