ansaurus

Question

Answer 1

A:

WHERE MONTH(current_time) = 4

as your timestamp is not a unix one.

also I suppose you want to check YEAR() as well

for the current period you can use corresponding function applied to the curdate() function, so, no need to explicitly set current month number

Col. Shrapnel 2010-04-30 08:53:42

I thought that may be the case, I have just tried - SELECT * FROM hits WHERE MONTH(current_time) = 4 and it is telling me that zero rows have been returned. Very strange?

Tom 2010-04-30 09:03:19

@Tom debugging is always a tool to use. What does MONTH() function return? is it the same as desired digit `4`? What does `MONTH(FROM_UNIXTIME(current_time))` return in real? Are there records to match? Why not to check and then adjust? Programming is NOT just plain writing. It is iterative process: write - test - check, write - test - check... Never assume, always verify. Do not stand helpless "I have just tried". Do something.

Col. Shrapnel 2010-04-30 09:11:58

Apologies, I have tried some debugging. Basically if I do SELECT Month('2010-04-30 10:17:32') I get '4' returned. If I do SELECT Month(current_time) FROM hits , each row is returned as NULL. The FROM_UNIXTIME query returns 1 for Month and 1970 for year. I have tried combinations of the Month(field) query but seem to be going in circles.

Tom 2010-04-30 09:34:49

@Tom it seems very strange. are you sure you have 2010-04-30 10:17:32 format in your current_time field?

Col. Shrapnel 2010-04-30 09:42:33

@Col. Shrapnel - it does seem strange. I'm absolutely positive about the field, it is set up as a timestamp field with the default value of current_timestamp - a value straight from that field is - 2010-04-29 20:40:30

Tom 2010-04-30 09:48:16

@Tom timestamp is a tricky column type in mysql. they changed it's format completely recently. Consider to use a datetime instead. If you try date_format() for this field, what will it return?

Col. Shrapnel 2010-04-30 09:55:48

@Col.Shrapnel - just before I read your last comment I dropped my table, renamed from hits to stats and the current_time field to time - just tried again and it seems to be working - SELECT Month(time) is now returning '4'. It's been very strange indeed. Thanks for all of your help with this, it's been very much appreciated. I will consider datetime in the future, may save me time.

Tom 2010-04-30 10:00:11

Yeah it helps sometimes :)

Col. Shrapnel 2010-04-30 10:28:22

Answer 2

A:

Lots of suggestions on the web

Check out

http://www.webmasterworld.com/forum88/13418.htm

aioobe 2010-04-30 08:55:52

ansaurus

tags:

views:

answers:

Querying results on timestamp in mysql

related questions