Jump to content


Photo

Newb date question


  • Please log in to reply
6 replies to this topic

#1 redneonrt

redneonrt
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 08 June 2006 - 08:55 PM

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.

#2 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 08 June 2006 - 09:29 PM

It depends... What is the format of the inserted timestamp?
~ D Kuang

#3 redneonrt

redneonrt
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 08 June 2006 - 09:32 PM

[!--quoteo(post=381608:date=Jun 8 2006, 04:29 PM:name=poirot)--][div class=\'quotetop\']QUOTE(poirot @ Jun 8 2006, 04:29 PM) View Post[/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");


#4 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 08 June 2006 - 09:39 PM

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] * FROM table WHERE EXTRACT(MONTH FROM FROM_UNIXTIME(date)) = 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\" /]
~ D Kuang

#5 redneonrt

redneonrt
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 09 June 2006 - 03:59 PM

Im not having much luck making this work.

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

I get a mysql error.

Could someone provide another example please?


#6 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 09 June 2006 - 04:07 PM

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

You missed a FROM...
~ D Kuang

#7 redneonrt

redneonrt
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 09 June 2006 - 04:39 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users