0

i have the table NAMES

and i've the following register on this table

NAME='Jose'
PASSWD='Maria'
POINTS='2000'
TIMESTAMP='2011-03-17 21:16:17' //field tipe TIMESTAMP

and another register

NAME='Pedro'
PASSWD='Paulo'
POINTS='4000'
TIMESTAMP='2011-02-16 00:00:00'

if i made the query like this

SELECT NOME FROM NAMES WHERE TIMESTAMP < '2011-03-17'

this query will return only the NAME "Pedro", because his TIMESTAMP are smaller than data that i put in the query (2011-03-17)

but what i want is..

Let's imagine what in the day 15/03 Jose has 2000 points, and in the day 16/03 he has 5000 points. How do i know how many points he has in the day 15/03 on today (18/03)?. This MySQL field tipe "TIMESTAMP" will return to me only registers that was updated before the query data, but this is not what i want.

What i want is take the value X that their field has in the day Y.

Justin Johnson
  • 30,978
  • 7
  • 65
  • 89
Pedro Papadópolis
  • 1

1 Answers1

0
select nome from names where date(timestamp) = '2011-03-15';
Marc B
  • 356,200
  • 43
  • 426
  • 500
  • i'll try be more especific.. i want make a graphic of users account evolution, taking POINTS value of four days ago until today, putting they on the graphic. if i use the query select * from NAMES where TIMESTAMP = '2011-03-15'; this will return 0 registers, because the value of the field TIMESTAMP isn't 2011-03-15, in the really is 2011-03-18, you understand? – Pedro Papadópolis Mar 18 '11 at 17:49
  • So you want to have the query return a range of days, whether or not a user has any points registered for that day? – Marc B Mar 18 '11 at 17:58
  • exactly, i want to get these days with their values from an register. – Pedro Papadópolis Mar 18 '11 at 18:18
  • Can't do that without some SQL extensions that aren't on all servers. But you can generate a temporary table with the dates listed, and join your names table against that. – Marc B Mar 18 '11 at 18:34
  • http://stackoverflow.com/questions/4737378/generate-sql-temp-table-of-sequential-dates-to-left-outer-join-to – Marc B Mar 18 '11 at 19:05