SELECTing dates that are in a different format

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

cobweb34
Forum Newbie
Posts: 10
Joined: Wed Jan 27, 2010 5:50 am

SELECTing dates that are in a different format

Post 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?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: SELECTing dates that are in a different format

Post 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'
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
cobweb34
Forum Newbie
Posts: 10
Joined: Wed Jan 27, 2010 5:50 am

Re: SELECTing dates that are in a different format

Post 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?
User avatar
xjake88x
Forum Commoner
Posts: 50
Joined: Sun Aug 01, 2004 7:05 pm

Re: SELECTing dates that are in a different format

Post 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)
cobweb34
Forum Newbie
Posts: 10
Joined: Wed Jan 27, 2010 5:50 am

Re: SELECTing dates that are in a different format

Post 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!!
User avatar
xjake88x
Forum Commoner
Posts: 50
Joined: Sun Aug 01, 2004 7:05 pm

Re: SELECTing dates that are in a different format

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SELECTing dates that are in a different format

Post by Eran »

Working with numbers instead of native date/time types for dates is definitely the wrong way to go about it.
User avatar
xjake88x
Forum Commoner
Posts: 50
Joined: Sun Aug 01, 2004 7:05 pm

Re: SELECTing dates that are in a different format

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: SELECTing dates that are in a different format

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SELECTing dates that are in a different format

Post 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.
User avatar
xjake88x
Forum Commoner
Posts: 50
Joined: Sun Aug 01, 2004 7:05 pm

Re: SELECTing dates that are in a different format

Post 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!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SELECTing dates that are in a different format

Post 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.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: SELECTing dates that are in a different format

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SELECTing dates that are in a different format

Post 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.
User avatar
xjake88x
Forum Commoner
Posts: 50
Joined: Sun Aug 01, 2004 7:05 pm

Re: SELECTing dates that are in a different format

Post 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 :P It's alright though!

Oh by the way the speed difference is only tangible when using millions of rows (i.e: stat tracking).
Post Reply