CODEWITHSUNDEEP
mysqlgroup-bytimestamp
cbmtrx
cbmtrx

Reputation: 610

MySQL GROUP BY not working with identical TIMESTAMPs

I'm doing a SELECT DISTINCT on rows that have identical timestamps and specifying GROUP BY; it only finds one row, no doubt because it's disallowing duplicates.

I tried running an update to each row with NOW(), but GROUP BY is still seeing the timestamps as identical.

Any way to update all timestamps for a table while incrementing each a millisecond (or something)?

Upvotes: 0

Views: 88

Answers (2)

Ochi
Ochi

Reputation: 1478

DATE_ADD(timestampfield, INTERVAL '0.000001' SECOND_MICROSECOND);

that way you can increment microseconds in mysql

Upvotes: 0

invisal
invisal

Reputation: 11171

Any way to update all timestamps for a table while incrementing each a millisecond (or something)?

This is for updating each rows based on what you need (instead of millisecond, it increase by second)

SET @COUNTER = 0;
UPDATE table SET timestamp_col = NOW() 
+ INTERVAL (@COUNTER := @COUNTER + 1) SECOND;

but if you don't care about increase by one second, but only want unique timestamps, you can try

UPDATE table SET timestamp_col = NOW() + INTERVAL id SECOND;

Given that id is an auto-incremental column.

Upvotes: 2

Related Questions