How to transform date into number?

[toplisek]

I have set query like:
SELECT userid, usernamejoindate, lastactivity
FROM user
AND joindate <1298937600
AND lastactivity - joindate < 1814400

How to set JOINDATE to my date as it is number?
1298937600

[penders]

Are you storing your dates in your DB as a unix timestamp (ie. a number)? Or are they stored as MySQLs DATETIME data types? And you need to convert from PHP's unix timestamp (a number) to MySQLs date format?

[toplisek]

It is date inside database like:
2011-02-21

but when I query I need probably number like posted 12...

[penders]

You can use MySQLs FROM_UNIXTIME() function to convert PHP's unix timestamp to MySQLs DATETIME data type and do this entirely within your SQL query...

SELECT userid, username, joindate, lastactivity  
FROM user  
AND joindate < FROM_UNIXTIME(1298937600) 
AND DATEDIFF(lastactivity,joindate) < 21

DATEDIFF() returns the difference between the dates in days. 1814400 secs / 86400 (secs in 1 day) = 21 days.

[toplisek]

How I know <1298937600 to my known date like June24

[penders]

There are quite a few different ways to manipulate dates between PHP and MySQL. Some ways will be better suited to your application...

I'm not quite sure what you are asking, but here's a couple of ideas...

echo strtotime('June 24 2011');

This returns a unix timestamp of the form 1308870000. However, the value returned is based on your local timezone (or the timezone of your server). See also mktime() and gmmktime().

date('Y-m-d H:i:s',1298937600);

This returns a string of the form '2011-03-01 00:00:00' that you could use directly in your MySQL query. Again, the value returned is based on your local timezone. See gmdate() to return UTC time.

[toplisek]

Thank you. It works.