MySQL date casting..

Forgive me that this isn't really PHP related, but solely MySQL.. but the MySQL mailing lists drive me nuts and figured someone here would have a quick answer.

I'm trying to sort by a date and time field(s) (two separate fields). It's a dumb system but until we do the next revision, it's going to stay the way it is (boss' orders) so bear with me.

Example:

ApptDate~ApptTime
2005-11-02~01:00 PM
2005-10-27~07:00 PM
2005-06-25~10:30 AM
0000-00-00~N/A
0000-00-00~N/A
0000-00-00~N/A
0000-00-00~06:30 PM

See? Dumb.. hah..

So I thought I could do something like this:

select ApptDate, ApptTime, DATE_FORMAT(CONCAT(ApptDate, ' ', ApptTime), '%Y-%m-%d %H:%i:%s') from Table


But it doesn't like "06:00 PM".. returns null on the items that have a valid date and time because the time format isn't what it wants. If I try it with a "06:00:00 PM" time, it makes it 6am.

Using STR_TO_DATE() does exactly the same thing.


You'd think STR_TO_DATE() would behave more like PHP's strtotime() but apparently not.


I can code a big complicated conditional SQL statement, but I'm hoping there's a way to convert at least the valid date/time pairs into a happily ORDER BY'd column. I can handle the 0000-00-00 and N/A entries with exceptions if I need to.


And I would really like to do this without pre-loading the data into PHP and sorting it with PHP's sort functions.


Any MySQL gurus who can show me what I'm missing here? Thanks in advance!

-TG

___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL date casting..

> Forgive me that this isn't really PHP related, but solely MySQL.. but
> the MySQL mailing lists drive me nuts and figured someone here would
> have a quick answer.
>
> I'm trying to sort by a date and time field(s) (two separate fields).
> It's a dumb system but until we do the next revision, it's going to stay
> the way it is (boss' orders) so bear with me.
>
> Example:
>
> ApptDate~ApptTime
> 2005-11-02~01:00 PM
> 2005-10-27~07:00 PM
> 2005-06-25~10:30 AM
> 0000-00-00~N/A
> 0000-00-00~N/A
> 0000-00-00~N/A
> 0000-00-00~06:30 PM
>
> See? Dumb.. hah..
>
> So I thought I could do something like this:
>
> select ApptDate, ApptTime, DATE_FORMAT(CONCAT(ApptDate, ' ', ApptTime),
> '%Y-%m-%d %H:%i:%s') from Table
>
>
> But it doesn't like "06:00 PM".. returns null on the items that have a
> valid date and time because the time format isn't what it wants. If I
> try it with a "06:00:00 PM" time, it makes it 6am.
>
> Using STR_TO_DATE() does exactly the same thing.
>
>
> You'd think STR_TO_DATE() would behave more like PHP's strtotime() but
> apparently not.
>
>
> I can code a big complicated conditional SQL statement, but I'm hoping
> there's a way to convert at least the valid date/time pairs into a
> happily ORDER BY'd column. I can handle the 0000-00-00 and N/A entries
> with exceptions if I need to.
>
>
> And I would really like to do this without pre-loading the data into PHP
> and sorting it with PHP's sort functions.
>
>
> Any MySQL gurus who can show me what I'm missing here? Thanks in
> advance!

What's wrong with this?

mysql> select str_to_date('2005-10-27 07:00 PM', '%Y-%m-%d %l:%i %p');
+---------------------------------------------------------+
| str_to_date('2005-10-27 07:00 PM', '%Y-%m-%d %l:%i %p') |
+---------------------------------------------------------+
| 2005-10-27 19:00:00 |
+---------------------------------------------------------+
1 row in set (0.04 sec)

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: MySQL date casting..

Unfortunately, no. The dates and times are stored as text. So here's what I get:

2006-01-10 07:00 PM
2006-01-10 08:00 PM
2006-01-10 09:00 AM
2006-01-10 09:00 PM


(notice the "AM" out of order)

For anyone interested, here's the big ugly version.. if anyone knows of a function that I can use instead of all this garbage, let me know:


select ApptDate, ApptTime, CONCAT(ApptDate, ' ', IF(SUBSTRING(ApptTime, 7, 2) = 'PM', CONCAT(SUBSTRING(ApptTime, 1, 2) + 12, ':', SUBSTRING(ApptTime, 4, 2), ':00'), IF(SUBSTRING(ApptTime, 7, 2) = 'AM', CONCAT(SUBSTRING(ApptTime, 1, 2), ':', SUBSTRING(ApptTime, 4, 2), ':00'), '00:00:00'))) AS ProperDateTime
from SomeTable

I'm not worried about blank or 0000-00-00 type dates in this case so don't really account for them. And again, this is meant to work on separate date and time fields in the following formats:

ApptDate: YYYY-MM-DD (we at least did that part 'normal')
ApptTime: HH:MM [AM|PM]

Thanks for the response though Stephen... always worth going over the simple stuff first. :)

-TG


= = = Original message = = =

Could you not just add:

ORDER BY ApptDate, ApptTime

To the end of your SQL - this will order the set by date then time. You can
also add ASC or DESC for Ascending or Dscending as desired.

N.B. the ORDER BY .... statement must be the last part of you query!


-----Original Message-----
From: tg-php@gryffyndevelopment.com [mailto:tg-php@gryffyndevelopment.com]
Sent: 17 January 2006 16:42
To: php-db@lists.php.net
Subject: [PHP-DB] MySQL date casting..

Forgive me that this isn't really PHP related, but solely MySQL.. but the
MySQL mailing lists drive me nuts and figured someone here would have a
quick answer.

I'm trying to sort by a date and time field(s) (two separate fields). It's
a dumb system but until we do the next revision, it's going to stay the way
it is (boss' orders) so bear with me.

Example:

ApptDate~ApptTime
2005-11-02~01:00 PM
2005-10-27~07:00 PM
2005-06-25~10:30 AM
0000-00-00~N/A
0000-00-00~N/A
0000-00-00~N/A
0000-00-00~06:30 PM

See? Dumb.. hah..

So I thought I could do something like this:

select ApptDate, ApptTime, DATE_FORMAT(CONCAT(ApptDate, ' ', ApptTime),
'%Y-%m-%d %H:%i:%s') from Table


But it doesn't like "06:00 PM".. returns null on the items that have a valid
date and time because the time format isn't what it wants. If I try it with
a "06:00:00 PM" time, it makes it 6am.

Using STR_TO_DATE() does exactly the same thing.


You'd think STR_TO_DATE() would behave more like PHP's strtotime() but
apparently not.


I can code a big complicated conditional SQL statement, but I'm hoping
there's a way to convert at least the valid date/time pairs into a happily
ORDER BY'd column. I can handle the 0000-00-00 and N/A entries with
exceptions if I need to.


And I would really like to do this without pre-loading the data into PHP and
sorting it with PHP's sort functions.


Any MySQL gurus who can show me what I'm missing here? Thanks in advance!

-TG


___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL date casting..

Ahh! Thank you Philip! That's what I was looking for! I see what I did wrong now.

I was using the date format strings wrong. I was using it like I'd use it for DATE_FORMAT() instead of as an input filter.

This is what I was trying to do:
select STR_TO_DATE('2003-11-05 06:00 PM', '%Y-%m-%d %H:%i:%s')


duh... hah. Thanks! I knew it was something simple.

-TG

------------------
What's wrong with [using] this?

mysql> select str_to_date('2005-10-27 07:00 PM', '%Y-%m-%d %l:%i %p');
+---------------------------------------------------------+
| str_to_date('2005-10-27 07:00 PM', '%Y-%m-%d %l:%i %p') |
+---------------------------------------------------------+
| 2005-10-27 19:00:00 |
+---------------------------------------------------------+
1 row in set (0.04 sec)



___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL date casting..

Youcould also try CASTing
(http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html) the data

Bastien

>From:
>To:
>CC:
>Subject: Re: [PHP-DB] MySQL date casting..
>Date: Tue, 17 Jan 2006 12:51:44 -0500
>
>Ahh! Thank you Philip! That's what I was looking for! I see what I did
>wrong now.
>
>I was using the date format strings wrong. I was using it like I'd use it
>for DATE_FORMAT() instead of as an input filter.
>
>This is what I was trying to do:
>select STR_TO_DATE('2003-11-05 06:00 PM', '%Y-%m-%d %H:%i:%s')
>
>
>duh... hah. Thanks! I knew it was something simple.
>
>-TG
>
>------------------
>What's wrong with [using] this?
>
>mysql> select str_to_date('2005-10-27 07:00 PM', '%Y-%m-%d %l:%i %p');
>+---------------------------------------------------------+
>| str_to_date('2005-10-27 07:00 PM', '%Y-%m-%d %l:%i %p') |
>+---------------------------------------------------------+
>| 2005-10-27 19:00:00 |
>+---------------------------------------------------------+
>1 row in set (0.04 sec)
>
>
>
>___________________________________________________________
>Sent by ePrompter, the premier email notification software.
>Free download at http://www.ePrompter.com.
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php