Is MySQL now() interval in milliseconds possible?

Question

I'm trying to only select the rows within 500 MS but I'm not sure if this is even possible with just MySQL?

My current code is:

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

I was unable to insert that in a code block.

welcome. Have a look at https://stackoverflow.com/q/6102744/1415724 --- https://stackoverflow.com/q/2825838/1415724 --- https://stackoverflow.com/q/9624284/1415724 - I think the last one could be your best bet. — Funk Forty Niner, Jun 10 '17 at 15:07

score 3 · Accepted Answer · answered Jun 10 '17 at 15:20

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.

Thank you. I should have said that I was using DATETIME, but this works for DATETIME aswell. — Persson, Jun 10 '17 at 21:53

score 2 · Answer 2 · answered Jun 10 '17 at 15:21

To convert and select time into milliseconds Use the UNIX_TIMESTAMP function.

SELECT chat_user, chat_msg, (UNIX_TIMESTAMP(chat_time)*1000) As chattime FROM chat_msg

UNIX_TIMESTAMP will get you seconds and you need to multiply by 1000 to get milliseconds.

Is MySQL now() interval in milliseconds possible?

2 Answers2