Jump to content

Archived

This topic is now archived and is closed to further replies.

redneonrt

Newb date question

Recommended Posts

I have php insert a timestamp for each enrty in my db table.

I would like to be able to seach for all entries from a particular month say May for example.

But Im not quite sure how to go about this.

Share this post


Link to post
Share on other sites
It depends... What is the format of the inserted timestamp?

Share this post


Link to post
Share on other sites
[!--quoteo(post=381608:date=Jun 8 2006, 04:29 PM:name=poirot)--][div class=\'quotetop\']QUOTE(poirot @ Jun 8 2006, 04:29 PM) [snapback]381608[/snapback][/div][div class=\'quotemain\'][!--quotec--]
It depends... What is the format of the inserted timestamp?
[/quote]

when I insert a date, strtotime

example $date = strtotime("06/07/2006");

Share this post


Link to post
Share on other sites
OK, that means it's using an UNIX timestamp.

You can use the following query:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * [color=green]FROM[/color] [color=orange]table[/color] [color=green]WHERE[/color] EXTRACT(MONTH [color=green]FROM[/color] [color=orange]FROM_UNIXTIME(date))[/color] [color=orange]=[/color] month_here [!--sql2--][/div][!--sql3--]

Now, I usually would use PHP to get this. But I'm following Barand so I'll use the least PHP possible. I hope it's a good thing [img src=\"style_emoticons/[#EMO_DIR#]/smile.gif\" style=\"vertical-align:middle\" emoid=\":smile:\" border=\"0\" alt=\"smile.gif\" /]

Share this post


Link to post
Share on other sites
Im not having much luck making this work.

I tried this [code]SELECT *
FROM `ext`
WHERE EXTRACT(
MONTH FROM_UNIXTIME( date ) ) =06[/code]

I get a mysql error.

Could someone provide another example please?

Share this post


Link to post
Share on other sites
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * [color=green]FROM[/color] [color=orange]`ext`[/color] [color=green]WHERE[/color] EXTRACT(MONTH [color=green]FROM[/color] [color=orange]FROM_UNIXTIME(date))[/color] [color=orange]=[/color] 6 [!--sql2--][/div][!--sql3--]

You missed a FROM...

Share this post


Link to post
Share on other sites
Woops [img src=\"style_emoticons/[#EMO_DIR#]/huh.gif\" style=\"vertical-align:middle\" emoid=\":huh:\" border=\"0\" alt=\"huh.gif\" /]

Thanks for your help though.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.