Organize by date and then time

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

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Organize by date and then time

Post by Luke »

Say I have two fields (mysql)-- date, and time... can I do something like...

Code: Select all

SELECT * FROM events ORDER BY date, time
I know I could just try it, but then I'd have to create a table, fill it with data, and all that... I just want to know if you can order by date, but secondary by time. So January 1st, 2006 @ 10:00am would be before January 1st, 2006 @ 12:00am.

Does that make sense?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

yes.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

:D
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Organize by date and then time

Post by timvw »

The Ninja Space Goat wrote: I know I could just try it, but then I'd have to create a table, fill it with data, and all that...
Does that make sense?
No it doesn't make much sense... Imho the fastest way is consulting http://dev.mysql.com/doc/refman/5.0/en/select.html...
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

well actually I was asking if my question makes sense, but thanks. I'm not sure why, but I never even think to check the mysql manual. Any time I have a question about anything, I always check it's documentation first, but for some reason I never do that with mysql. Weird.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

Is it just me, or is the PHP manual waaaay easier to read / consult than the MySQL manual?

The PHP folks did an awesome job - definitely lessons to be learned there!

Cheers,
Kieran
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Re: Organize by date and then time

Post by volka »

The Ninja Space Goat wrote:I know I could just try it, but then I'd have to create a table, fill it with data, and all that... I just want to know if you can order by date, but secondary by time. So January 1st, 2006 @ 10:00am would be before January 1st, 2006 @ 12:00am.
So there is no such table and you're free to choose the structure? Then why not using a single DATETIME field?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

because there may or may not be a time and there may or may not be a date
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

Why not have all those fields? A unix timestamp field would compliment year, month, date, hour, minute, second.. whatever! Don't oversimplify yourself into a corner!

Besides, most database records are write once / read many, so it would add less calculation overhead to generate these fields once on the way in then many times on the way out...

What's a few redundant bytes in your database compared to your mental health?

Cheers,
Kieran
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Kieran Huggins wrote:Why not have all those fields? A unix timestamp field would compliment year, month, date, hour, minute, second.. whatever! Don't oversimplify yourself into a corner!

Besides, most database records are write once / read many, so it would add less calculation overhead to generate these fields once on the way in then many times on the way out...

What's a few redundant bytes in your database compared to your mental health?

Cheers,
Kieran
unix timestamps are limited to dates > December of 1969... I need dates further back than that.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You could always use adjusted Julian dates. ;) MySQL supports, that I remember a good range when using the separate types for date and time, so it should be fine there.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

The Ninja Space Goat wrote:unix timestamps are limited to dates > December of 1969... I need dates further back than that.
I believe the can become negative, unless you're on windows.
Post Reply