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
Solved

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

Posted on 2008-06-10
9
323 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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