Solved

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

Posted on 2008-06-10
9
325 Views
Last Modified: 2010-04-21
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
Comment
Question by:richardsimnett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 21751824
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
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 21751837
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
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 21751910
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:richardsimnett
ID: 21752705
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
 

Author Comment

by:richardsimnett
ID: 21752797
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
 
LVL 17

Accepted Solution

by:
Aleksandar Bradarić earned 500 total points
ID: 21752809
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
 

Author Comment

by:richardsimnett
ID: 21752825
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
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 21752905
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
 

Author Closing Comment

by:richardsimnett
ID: 31465758
Thanks!
0

Featured Post

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question