mysql, php, Uncategorized

using timestamp in where clause with mysql


i spend alot of time search how to use the timestamp in where clause with mysql finally i made, it was like i had time stamp stored in my data base i need to fetch data by year, i need some thing like

$sql = “SELECT * from tbl where timestamp = ‘2011’ “;

so i searched it alot and finally via mysql official site i found way out of it using mysql inbuilt function as below

mysql> SELECT EXTRACT(YEAR FROM ‘2009-07-02’);
-> 2009

mysql> SELECT FROM_UNIXTIME(1196440219);
-> ‘2007-11-30 10:30:19’

then i combine it like …..

$sql = “SELECT * from tbl where EXTRACT(YEAR FROM FROM_UNIXTIME(timestamp)) = ‘2011’”

check this site for more information

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_extract

cheers……….

Advertisements

2 thoughts on “using timestamp in where clause with mysql”

  1. Thanks for the marvelous posting! I actually enjoyed reading it, you will be a great author.
    I will ensure that I bookmark your blog and definitely will
    come back at some point. I want to encourage you to definitely continue your great job, have a
    nice morning!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s