Get records in Mysql where unix timestamp is today

user_739035 0
Viewed 2937

I'm storing records in msyql where a resolve_by column has a unix timestamp.

I'm trying this query:

SELECT id FROM tickets WHERE FROM_UNIXTIME('resolve_by','%Y-%m-%d') = CURDATE()

The basic table structure is:

id|resolve_by|date_created
4, 1506092040, 1506084841

But this is returning 0 records. How can I get records where the unix timestamp value = today's date?

Thanks,

unix-timestamp mysql
2 Answers

Changed query from :

SELECT id FROM tickets WHERE FROM_UNIXTIME('resolve_by','%Y-%m-%d') = CURDATE()

To: 

SELECT id FROM tickets WHERE FROM_UNIXTIME(resolve_by,'%Y-%m-%d') = CURDATE()

It's working now.

user_7390350

In general you'll want to avoid using functions on the columns side of where conditions, as it will most probably disqualify your query to benefit from indexes.

Consider something like:

create table test_table ( id varchar(36) primary key, ts timestamp );

insert into test_table (id,ts) values('yesterday',     current_timestamp - interval 1 day);
insert into test_table (id,ts) values('midnight',      current_date);
insert into test_table (id,ts) values('now',           current_timestamp);
insert into test_table (id,ts) values('next midnight', current_date + interval 1 day);
insert into test_table (id,ts) values('tomorrow',      current_timestamp + interval 1 day);

create index test_table_i1 on test_table (ts);

select *
  from test_table
 where ts >= current_date
   and ts <  current_date + interval 1 day;
;

PS: you can also use 

select *
  from test_table
 where ts between current_date and current_date + interval 1 day;

if you're not picky about excluding next midnight (between accepts both boundaries)

user_4286270
Related