0

There is an INT field which I store timestamps in table.

I am trying to select rows 1 week before this timestamp

WHERE last_loc_date > DATE(NOW()) - INTERVAL 1 WEEK

Doesn't work. Any idea?

Utku Dalmaz
  • 9,780
  • 28
  • 90
  • 130

2 Answers2

0

You could use unix_timestamp to calculate second argument:

WHERE last_loc_date > unix_timestamp(DATE(NOW()) - INTERVAL 1 WEEK)

Please avoid using FROM_UNIXTIME(last_loc_date) > DATE(NOW()) - INTERVAL 1 WEEK. This condition is not SARGable unless you use function-based index.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • "This condition is not SARGable" About time that MySQL supports native expression/functions indexing like PostgreSQL does.. No more need to simulate it with generated columns – Raymond Nijland Jun 04 '18 at 12:00
  • @RaymondNijland Great, but still user has to create function index. If possible we should avoid such constructs. – Lukasz Szozda Jun 04 '18 at 12:01
  • "but still user has to create function index. If possible we should avoid such constructs" Great the user needs to create a index annyway also for this query.. Or the user creates a t-bree index or a expression/functions index it doesn't matter in mine opinion – Raymond Nijland Jun 04 '18 at 12:07
  • @RaymondNijland I've assumed that this index may exists. As a rule of thumb - create objects that support as many scenarios as possible. Anway could you provide link to doc where it states that MySQL 8.0 supports expression in indexes? – Lukasz Szozda Jun 04 '18 at 12:08
  • "Anway could you provide link to doc where it states that MySQL 8.0 supports expression in indexes?" Generated column indexing is MySQL 5.7+ not MySQL 8.0.. "Generated columns can simulate functional indexes: Use a stored column to define a functional expression and index it." source https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html Also can index JSON data https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html – Raymond Nijland Jun 04 '18 at 12:13
  • @RaymondNijland Ok, I know that workaround. I thought I could make it directly as in PostgreSQL/Oracle `CRATE INDEX idx ON tab(UPPPER(col))` – Lukasz Szozda Jun 04 '18 at 12:15
0

Don't use DATE(now()) if you want respect the strictly one week

WHERE last_loc_date > unix_timestamp(NOW() - INTERVAL 1 WEEK)
A. Colonna
  • 852
  • 7
  • 10