CODEWITHSUNDEEP
mysqlsql
Amiga500
Amiga500

Reputation: 6131

Select a record based on timestamp

I have a table that has a column row filled with a Javascript's, Date.now() function. The column name is update_time, and its sample value is 1571152209099.

How can I make a Select for all rows that have todays date (taking into account only year, month and a day)?

I have tried something like this, but it get's me nowhere.

Select *
from program_end
where workout_rate between 0 and 1
  AND FROM_UNIXTIME(update_time, '%YYYY-%MM-%DD') = CURDATE()

I also tried changing the time format:

Select *
from program_end
where FROM_UNIXTIME(update_time, '%Y-%m-%d') = CURDATE()

But no result whatsoever. Any insight is appreciated.

Upvotes: 0

Views: 82

Answers (3)

spencer7593
spencer7593

Reputation: 108370

For insight, consider:

 SELECT '1970-01-01' + INTERVAL ( 1571152209099 /1000) SECOND

returns:

_dt                         
--------------------------
2019-10-15 15:10:09.099000

We recognize that the 1571152209099 value from JavaScipt Date.now() is integer milliseconds from the beginning of the era (Jan 1, 1970 midnight UTC).

Also consider:

SELECT TIMESTAMPDIFF(SECOND,'1970-01-01 00:00','2019-10-15 00:00')*1000 AS _dt_1
     , TIMESTAMPDIFF(SECOND,'1970-01-01 00:00','2019-10-16 00:00')*1000 AS _dt_2

returns:

        _dt_1           _dt_2
-------------  --------------
1571097600000   1571184000000

The TIMESTAMPDIFF function is returning a difference in seconds; we multiply by 1000 to get milliseconds.

We recognize that any JavaScript Date.now() milliseconds value that is greater than or equal to _dt_1 and is less than _dt_2 occurs sometime "on" the date '2109-10-15'

So, given update_time is milliseconds since the beginning of the era...

In a query, I would compare the bare update_time column to two literal values:

WHERE update_time >= 1571097600000 
  AND update_time <  1571184000000

Referencing the bare column has the advantage that MySQL can make use of an efficient range scan operation, with a suitable index available.

To derive those millisecond values from a given date value, we can do a calculation in an expression, like this:

WHERE update_time >= TIMESTAMPDIFF(SECOND,'1970-01-01', '2019-10-15' )*1000 
  AND update_time <  TIMESTAMPDIFF(SECOND,'1970-01-01', '2019-10-15' + INTERVAL 1 DAY)*1000
                                                         ^^^^^^^^^^

Those expressions on the right side get evaluated just one time at the start of the statement execution.

Note that if we wrap update_time in a function, then that function has to be evaluated for every value of update_time, for every row in the table (that isn't filtered out by some other predicate.)

Upvotes: 2

pilcrow
pilcrow

Reputation: 58524

Javascript's Date.now is giving you milliseconds, whereas MySQL's FROM_UNIXTIME expects seconds.

Unable to understand the epoch timestamp in milliseconds, FROM_UNIXTIME is returning NULL, which is of course failing to match the current date.

mysql> SELECT FROM_UNIXTIME(1571152209099);
+------------------------------+
| FROM_UNIXTIME(1571152209099) |
+------------------------------+
| NULL                         |  -- THIS IS YOUR PROBLEM
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(1571152209099/1000);
+-----------------------------------+
| FROM_UNIXTIME(1571152209099/1000) |
+-----------------------------------+
| 2019-10-15 10:10:09.0990          |
+-----------------------------------+
1 row in set (0.00 sec)

Try this:

FROM_UNIXTIME(FLOOR(update_time/1000), format...)

Upvotes: 1

GMB
GMB

Reputation: 222382

If you don't have dates in the future, you can simply do:

WHERE update_time >= UNIX_TIMESTAMP(CURDATE())

This will happily use an existing index on update_time.

If you have dates in the future that you need to exclude from the resultset, then:

WHERE 
    update_time >= UNIX_TIMESTAMP(CURDATE()) 
    AND update_time < UNIX_TIMESTAMP(CURDATE() + INTERVAL 1 DAY)

Upvotes: 1

Related Questions