Page 1 of 1
Organize by date and then time
Posted: Thu Dec 21, 2006 12:31 pm
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?
Posted: Thu Dec 21, 2006 12:33 pm
by John Cartwright
yes.
Posted: Thu Dec 21, 2006 12:34 pm
by Luke
Re: Organize by date and then time
Posted: Thu Dec 21, 2006 4:20 pm
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...
Posted: Thu Dec 21, 2006 4:23 pm
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.
Posted: Thu Dec 21, 2006 4:28 pm
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
Re: Organize by date and then time
Posted: Thu Dec 21, 2006 4:37 pm
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?
Posted: Thu Dec 21, 2006 5:53 pm
by Luke
because there may or may not be a time and there may or may not be a date
Posted: Thu Dec 21, 2006 10:59 pm
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
Posted: Wed Dec 27, 2006 7:00 pm
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.
Posted: Wed Dec 27, 2006 8:18 pm
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.
Posted: Thu Dec 28, 2006 3:17 am
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.