CODEWITHSUNDEEP
phpmysql
max
max

Reputation: 3716

getting first 5 digits of a database stored number in the query

I'm working with a table and there is field in my table which stores raw time() function value as date.

I want to get rows with today date from this table .

So i figure out when time() func returns a 10 digit number like 1316352184 the first 5 digits are for year , month , day which i need for getting today's date and the rest is for hour minute Second which i dont need

So i get today without hour and... like 

$t = time();
$t = $t /100000;
$today =(int)$t;

Now i need to get rows with today date from the table but i'm not sure how to do that.

How can i get first 5 digits of stored date in database in my query to compare it with $date?

Something like this:

$sql = "SELECT * FROM TABLE WHERE ((int)date/100000) as date = $today ;

Upvotes: 0

Views: 360

Answers (4)

Nicola Cossu
Nicola Cossu

Reputation: 56357

select * from table
where from_unixtime(unix_timestamp_field,'%Y-%m-%d') = curdate()

Upvotes: 2

FtDRbwLXw6
FtDRbwLXw6

Reputation: 28891

Sounds like you should use the DATETIME or TIMESTAMP data type for your column so you can use MySQL's date functions.

Upvotes: 0

Jonnix
Jonnix

Reputation: 4187

What you have is a UNIX timestamp. The number of seconds since January 1st, 1970.

You can use date() and mktime() to work out what todays timestamp is, then do date > the timestamp. If that make sense.

Upvotes: 0

Tadas Kvedaras
Tadas Kvedaras

Reputation: 547

Why you don't use: 

$sql = "SELECT * FROM TABLE WHERE date(date) = date(NOW());

Upvotes: 0

Related Questions