• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

How would I convert this varchar date field to a usable date format for queries?

Hello,
I have a table which I need to be able to query by date. The date is stored as an email compliant RFC date, which looks like this:

Sun, 24 Feb 2008 02:38:57 -0500 (EST)

I need to be able to query against this table by time and date. So for instance, I need to know all the transaction id's that occurred between Feb 24 2008, between the times of 1:30pm and 2:30pm.

How would I do this with mysql? Changing the format of the date field is not an option.


Worth 500 points.

Thanks,
Rick
0
richardsimnett
Asked:
richardsimnett
  • 5
  • 4
1 Solution
 
Aleksandar BradarićSoftware DeveloperCommented:
In plain SQL you would have to cut out the substrings and paste together a date time value :( ( I can put together an example if you need it?

If using a script language (e.g. PHP) is an option, then maybe like this:
---
$email_date = "Sun, 24 Feb 2008 02:38:57 -0500 (EST)";
$mysql_date = date("Y-m-d H:i:s", strtotime($email_date));
echo $mysql_date;
---
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Or you could try MySQL's `STR_TO_DATE()` function - see more info here:http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date
0
 
Aleksandar BradarićSoftware DeveloperCommented:
This should do the trick:
---
mysql> SELECT STR_TO_DATE("Sun, 24 Feb 2008 02:38:57 -0500 (EST)", '%a, %d %b %Y %T') AS mysql_date;
+---------------------+
| mysql_date          |
+---------------------+
| 2008-02-24 02:38:57 |
+---------------------+
1 row in set, 1 warning (0.00 sec)
---
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
richardsimnettAuthor Commented:
leannon,
Ok I see how that works, but I still dont understand how to perform the query. Lets suppose this:

I need to select all the id's between Jun 9 2008, between the hours of 10:45AM and 12:00PM, How would I do it using the STR_TO_DATE you introduced to me before?

Thanks,
Rick
0
 
richardsimnettAuthor Commented:
leannon,
here is what I have of the query so far:

SELECT count(transaction_id) from transactions where DATE(STR_TO_DATE(rfcdate, '%a, %d %b %Y %T')) = '2008-06-09' and server = '216.117.206.9'

I need to some how add the logic for the time range after the server = statement in the where clause.

Thanks,
Rick
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Try this:
---
SELECT
  id
FROM
  yourTable
WHERE
  STR_TO_DATE(your_email_date_field, '%a, %d %b %Y %T') BETWEEN '2008-06-09 10:45:00' AND '2008-06-09 12:00:00';
---
0
 
richardsimnettAuthor Commented:
leannon,
near as I can figure it, the only way to do this (easily) is to somehow convert the date time to seconds since the start of the system clock, and look for values >= to the min secs, and values <= to the max secs.

Any idea how to do that?

Thanks,
Rick
0
 
Aleksandar BradarićSoftware DeveloperCommented:
And why not the SQL above?

> is to somehow convert the date time to seconds since the start of the system clock

It's not easier than the previous SQL:
---
SELECT
  id
FROM
  yourTable
WHERE
  UNIX_TIMESTAMP(STR_TO_DATE(your_email_date_field, '%a, %d %b %Y %T')) BETWEEN UNIX_TIMESTAMP('2008-06-09 10:45:00') AND UNIX_TIMESTAMP('2008-06-09 12:00:00');
---

Just added `UNIX_TIMESTAMP()` around each datetime...
0
 
richardsimnettAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now