Solved

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

Posted on 2008-06-10
9
324 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
updating the date data 12 40
two ways encryption with php 3 44
java mysql insert application 14 45
Inserting data into database 10 46
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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