Convert Date Format

Question

Hi,

I have a moodle installation in which there is a column in mdl_user table called firstaccess whose type is bigint(10) and contains date in following format 1266839570.

I am writing a query for accessing users according to date filters. For e.g. i want to check which users firstaccess is greater than '2010-04-12'. How can i convert the date? These two date formats are different. I think firstaccess is unix timestamp. Should i change the '2010-04-12' into unix timestamp or there is a way to convert firstaccess i.e 1266839570 to yyyy-mm-dd format.

Please help me on this.

Thanks

Answer 1

You can create a unix timestamp in php with the mktime() function, then simply put it in your query.
MySQL has a date_format() function, that can format dates however you like, but I'm not sure if it works with bigints. You'd better go with the mktime.

Answer 2

date() and mktime() are functions to concert from unix timestamp and back.
You can convert your dates in either way

Answer 3

I believe you can write your query using a timestamp. Eg.

SELECT * FROM mytable WHERE firstaccess >= TIMESTAMP('2010-04-12')

http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

Answer 4

I don't know what form the date in your form is, but you can easily convert it to a timestamp (if it already isn't one) using mktime. For example:

$mytimestamp=mktime(0,0,0, $month, $day, $year);

Then just add it to your query:

$myQuery= "SELECT whatever FROM sometable WHERE " . $mytimestamp . ">=firstaccess";

Answer 5

Like Paul Peelen, my answer is a MySQL query. I'm going the other way, though, and converting first access into a date. Using the date information in your problem:

SELECT * FROM mytable WHERE DATE_FORMAT(FROM_UNIXTIME(firstaccess), '%Y-%m-%d') > '2010-04-12';