Waterstone
asked on
Simple query using a timestamp field and a date range
New to mySQL. This is on a Linux box running MySQL 5.1.52
How do I do a simple query to retrieve e a date range from a timestamp field?.
The field is named time . I know, but I didn't name it. This is what I need to do iinteractively.
Select * from journal
WHERE time > 2009/12/31 AND
time < 2010/12/31
I also need to do the same in a form with two php form fields, startdate and endate, formatted as yyyy-mm-dd
Select * from diaries
WHERE time >startdate AND
time < enddate
Help please.
How do I do a simple query to retrieve e a date range from a timestamp field?.
The field is named time . I know, but I didn't name it. This is what I need to do iinteractively.
Select * from journal
WHERE time > 2009/12/31 AND
time < 2010/12/31
I also need to do the same in a form with two php form fields, startdate and endate, formatted as yyyy-mm-dd
Select * from diaries
WHERE time >startdate AND
time < enddate
Help please.
ASKER
Thanks, but it does not work.
Does not like the # signs. Turns the line into a comment.
Does not like the # signs. Turns the line into a comment.
ASKER
Sorry,w as not specific. I'm trying to run the query in mySQL, not a php page. That must be the php code.
I'll try that in pfp after I verify that the data is there using an interactive query using Navicat or MySQL Workbench.
I'll try that in pfp after I verify that the data is there using an interactive query using Navicat or MySQL Workbench.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, that worked. I was playing with date_format parameters and looking for a more complex issue. Field is named time, type is timestamp.
For future use, I'd suggest that you use unix_timestamp (int (12)) as the definition of the column which represents the number of seconds since january 1st 1970 GMT
IT simplifies queries such that you do not need to use date_add or similar functions to manipulate the date.
i.e. adding or subtracting 3600 from the column, will result in an hour shift eiher way.etc.
When displaying the unix_timestamp can then be converted for date display and provides for better customization where the Timezone of the client needs to be taken into account. i.e. one is in the EST, GMT, Australian etc. and the interface can simply grab the timestamp and during the display convert it into the date with the right timezone.
IT simplifies queries such that you do not need to use date_add or similar functions to manipulate the date.
i.e. adding or subtracting 3600 from the column, will result in an hour shift eiher way.etc.
When displaying the unix_timestamp can then be converted for date display and provides for better customization where the Timezone of the client needs to be taken into account. i.e. one is in the EST, GMT, Australian etc. and the interface can simply grab the timestamp and during the display convert it into the date with the right timezone.
WHERE time > #2009/12/31# AND
time < #2010/12/31#