Solved

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

Posted on 2008-06-10
9
322 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
  • 5
  • 4
9 Comments
 
LVL 17

Expert Comment

by:leannonn
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:leannonn
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:leannonn
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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:
leannonn 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:leannonn
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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