MYSQL Date Range RFC 2822

Question

THE SITUATION I pull timestamps formatted in RFC 2822 (Sat, 01 Dec 2012 05:49:45 +0000) and store them in a VARCHAR field in MYSQL. I have a start_date and end_date.

THE GOAL Search BETWEEN two dates (like start_date BETWEEN '2012-11-01' AND '2012-12-01')

THE CONDITIONS I want to do this with pure SQL and not do post processing in PHP

THE ACCEPTABLE COMPROMISE I don't want to, but I will convert and store them as DATETIME by using PHP if needed.

Can anyone help me accomplish my goal (listed above). Rick

fthiella · Accepted Answer · 2012-12-20T22:20:24.693

2

You could convert your string dates do datetime using str_to_date:

select str_to_date('Sat, 01 Dec 2012 05:49:45 +0000','%a, %d %b %Y %T')

If you need to convert also timezone, try this:

set @datestring='Sat, 01 Dec 2012 05:49:45 +0000';
select
  CONVERT_TZ(
    str_to_date(@datestring,'%a, %d %b %Y %T'),
    concat(mid(@datestring, 27, 3), ':', mid(@datestring, 30, 2)),
    '+00:00'
  )

edited Dec 20 '12 at 22:20

answered Dec 20 '12 at 22:06

fthiella

48,073
15
90
106

I was actually on this route, but I hit a wall when I got to the timezone. Theres no way to parse that using SQL. – Rick Scolaro Dec 20 '12 at 22:14
@RickS there's the function CONVERT_TZ, i think you can make it work, i'll update my answer – fthiella Dec 20 '12 at 22:16

score 1 · Answer 2 · answered Dec 20 '12 at 21:43

1

Store them as a native DATETIME. This is the only sane approach.

Why are you so opposed to using the proper tool for the job?

answered Dec 20 '12 at 21:43

tadman

208,517
23
234
262

I think I'll do this, as the table is very new and I can just begin converting the RFC2822 date into a datetime like this: `$timestamp = strtotime('Tue, 30 Sep 2008 10:30:00 EDT'); echo date('Y-m-d g:i:s a', $timestamp);` – Rick Scolaro Dec 20 '12 at 22:34
1

The sooner you can flip over to native data types, the better. Whenever possible use the appropriate column type. The only reason for storing string dates and times is if they're coming from a foreign data source and you want to be sure you've converted them correctly, preserving the original and converted values for comparison purposes. – tadman Dec 20 '12 at 23:54

score 0 · Answer 3 · answered Dec 20 '12 at 21:44

0

Storing timestamps as a string is poor use of the database features. Since they were all the same format, they could have easily been converted to a datetime on input.

answered Dec 20 '12 at 21:44

wallyk

56,922
16
83
148

MYSQL Date Range RFC 2822

3 Answers3