Page 1 of 2
SELECTing dates that are in a different format
Posted: Tue Feb 09, 2010 9:43 am
by cobweb34
Hi Guys,
I’m trying to execute a SQL query to give me the amount of records that were logged between 2 dates.
I’m using a pre-existing database which has formatted the dates like this:
dd/mm/yyyy hh:mm:ss
I’ve tried:
$hardQ = "SELECT COUNT (owner) as owner FROM opencall WHERE owner = 'jsmith' AND logdate < ISDATE('06/02/2010 00:00:00') AS 'DD/MM/YYYY HH:MM:SS AND logdate > ISDATE('09/02/2010 00:00:00') AS 'DD/MM/YYYY HH:MM:SS";
...but I’ve had no luck. Has anyone got any ideas?
Re: SELECTing dates that are in a different format
Posted: Tue Feb 09, 2010 9:53 am
by pickle
ISDATE() isn't a MySQL function & if it were, it would likely return TRUE or FALSE.
This should work as a WHERE clause:
Code: Select all
WHERE log_date BETWEEN '06/02/2010 00:00:00' AND '09/02/2010 00:00:00'
Re: SELECTing dates that are in a different format
Posted: Tue Feb 09, 2010 10:26 am
by cobweb34
Thanks for the advice Pickle but it's not working.
It's pulling records all the way back in 2008.
The earliest record pulled was logged 09/07/2008 08:05:31.
I'm using MS SQL if that makes any difference?
Re: SELECTing dates that are in a different format
Posted: Wed Feb 10, 2010 11:52 pm
by xjake88x
My favorite and most efficient way of storing and working with dates is in unix timestamp format. This way it's just an integer.
That way it's always in the same format and always very accurate. It's also quite easy to work with the data in PHP as well.
(Hope this helps)
Re: SELECTing dates that are in a different format
Posted: Thu Feb 11, 2010 3:52 am
by cobweb34
Jake thanks so much!!
It is a pre-existing SQL database but has another column called logdatex with a long int and I didn't know what it was..turns out it was a UNIX Timestamp!!
You're a life saver, thanks!!
Re: SELECTing dates that are in a different format
Posted: Thu Feb 11, 2010 6:18 pm
by xjake88x
Haha glad I could help! They are so easy to work with because to add and subtract timespans from them all you have to do is seconds * 60 for a minute, or * 60 * 60 * 24 for a day, etc. It's also faster for the database because it is a number.
Re: SELECTing dates that are in a different format
Posted: Thu Feb 11, 2010 6:21 pm
by Eran
Working with numbers instead of native date/time types for dates is definitely the wrong way to go about it.
Re: SELECTing dates that are in a different format
Posted: Thu Feb 11, 2010 7:21 pm
by xjake88x
pytrin wrote:Working with numbers instead of native date/time types for dates is definitely the wrong way to go about it.
Unix timestamp is as native as it gets. However I'm assuming you're talking about the whole adding up seconds and stuff, which is just an example of how simple processing that data is.
By the way I really like this site:
http://www.lionite.com
Re: SELECTing dates that are in a different format
Posted: Thu Feb 11, 2010 9:21 pm
by Weirdan
xjake88x wrote:They are so easy to work with because to add and subtract timespans from them all you have to do is seconds * 60 for a minute
This is the problem though -
not every minute has 60 seconds.
There's nothing easy about working with times and calendars, despite some people thinking the opposite.
Re: SELECTing dates that are in a different format
Posted: Fri Feb 12, 2010 2:02 am
by Eran
Unix timestamp is as native as it gets. However I'm assuming you're talking about the whole adding up seconds and stuff, which is just an example of how simple processing that data is.
1. MySQL timestamps are stored as UTC internally. This makes the data portable between servers and greatly simplifies working with timezones. The unix timestamp you are storing likely has a timezone offset
2. MySQL has a whole range of functions for its native date/time types
http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html
you can replicate that manually with considerable effort, but what is the point?
3. Using simplified number calculations as you suggested will not work beyond a week. A month has a variable amount of days, so you can't simply add 1 month or more without considering which month you start from and how many you are adding. MySQL handles that for you (using "INTERVAL n MONTH" etc). Not to mention leap years and other considerations
4. Unix timestamps have an upper limit at the year 2038. MySQL timestamps can go up to 9999
5. MySQL timestamps are human readable, making database administration easier
I'm sure there are more reasons, this just off the top of my head.
Re: SELECTing dates that are in a different format
Posted: Fri Feb 12, 2010 5:09 am
by xjake88x
Interesting about the 2038, did not know that—I'll have to start using mysql dates before too long!
A unix timestamp using time() though is still accurate I'm pretty sure.. Modifying it is possible with php functionality. Why else would it be a very standard format recognized by mysql, php, C++, etc.
I used to use mysql datetime but I always liked the speed of using an integer instead. Oh well maybe the minor speed difference is negligible. Thanks for the info!
Re: SELECTing dates that are in a different format
Posted: Fri Feb 12, 2010 7:23 am
by Eran
I'm not sure why you keep mentioning "speed differences". Did you benchmark it and found any such differences? even if there are, and I doubt it, it would be completely negligible compared to the actual run-time of the query. This is the very definition of premature optimization
I didn't say a unix timestamp is not accurate. If you read my post I've listed several reasons why it is preferable to use a MySQL timestamp and none was about accuracy of the timestamp itself. I did mention that manual dates addition/subtraction/timezone conversion is likely to be less accurate since those are not trivial calculations.
Re: SELECTing dates that are in a different format
Posted: Fri Feb 12, 2010 8:31 am
by JakeJ
xjake88x wrote:Interesting about the 2038, did not know that—I'll have to start using mysql dates before too long!
That 2038 limit only applies to 32 bit operating systems. 64 bit OS's are not limited that way. I forget how far the 64 bit goes but it's well beyond the lifespan of the great grand children of your great, great, great, great, great grandchildren.
Re: SELECTing dates that are in a different format
Posted: Fri Feb 12, 2010 8:58 am
by Eran
to be more accurate, it will go as far as 20 times the age of the current universe. Regardless, MySQL timestamps will work beyond 2038 even on 32bit systems.
Re: SELECTing dates that are in a different format
Posted: Fri Feb 12, 2010 8:48 pm
by xjake88x
The server I work on is 64 bit but I'm inclined to use the mysql datetime even though I do sense a little hostility coming from the datetime advocate

It's alright though!
Oh by the way the speed difference is only tangible when using millions of rows (i.e: stat tracking).