dear all,
I would like to know how to structure and SQL query to query between months ranges
Something along the lines of:
SELECT * FROM table WHERE datetime BETWEEN xx/xx/xx AND xx/xx/xx
I have a table with the following date field entry named datetime:
07-05-2009
Which I am assuming is an entry for 07-05-2009 (d/m/Y)
I have read the MySQL Reference on "date" but couldn't make much sense of it - SQL isn't my strong point.
Thanks in advance.
It would be better to store the dates in the database as a timestamp, and use these in the query. Take the normal date in PHP, use strtotime to convert it to a timestamp and search this.
Your queries will be faster if using integers rather than full text searching..
I have a table with the following date field entry named datetime:
07-05-2009
Which I am assuming is an entry for 07-05-2009 (d/m/Y)
or it could equally be m-d-y
thats the other reason why dates and times should be stored as a timestamp
timstamp is smaller faster and un-ambiguous
Thank a lot for xan and almostbob,
I have redesign my table to store the datetime into time stamp so it will be faster. Actually I have store it into varchar because my user prefer (d-m-Y) format than (Y-m-d).
Ok , thank a lot for your advices. It helps me lot
Thank a lot for xan and almostbob,
I have redesign my table to store the datetime into time stamp so it will be faster. Actually I have store it into varchar because my user prefer (d-m-Y) format than (Y-m-d).
Ok , thank a lot for your advices. It helps me lot
unix time is a number, not a varchar
it is 10 digits representing the number of seconds from 1/1/1970
February 9 had/was 1234567890, oclock in Unix time
Storing a varchar will not speed up operations or make the database smaller
the improvement comes from storing a timestamp [int(10)] and operating on it. You only need to parse the timestamp to human -readable on output, which is much faster, and you can with no further effort have output forms in ymd dmy mdy or any other user configured form
ignore completely the "date" data type, the data in its simplest form, is a number.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.