Solved

regular expression to see if mysql is a date

Posted on 2013-12-07
16
1,314 Views
Last Modified: 2013-12-16
2013-12-04 00:44:52
2013-12-04 00:45:53
2013-12-04 00:46:58

what is the regular expression for mysql (not using php) to see if varchar is written like a date format

 I need to do this to move away from table that has varchar so I can move into a table that has datetime column
0
Comment
Question by:rgb192
  • 4
  • 4
  • 3
  • +3
16 Comments
 
LVL 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 250 total points
ID: 39703239
A little long, but it should work. I can't remember what all shorthand MySQL supports, but you might be able to replace each [0-9] with a \d.

[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]

Open in new window

0
 
LVL 18

Expert Comment

by:Matthew Kelly
ID: 39703246
Well, not sure if this is the best (as it would allow for February 31st, or other months that don't have 31 days) but an easy one to do would be just to match each set of numbers:

^([1-2][0-9]{3,3})-(0?[1-9]|1[0-2])-(0?[1-9]|[1-2][1-9]|3[0-1])\s([0-1][0-9]|2[0-4]):([0-5][0-9]):([0-5][0-9])$

Open in new window


This would match years 1000-2999, and then months 1-12, days 1-31, hours 0-24 and minutes/seconds 0-59 basically expecting a string to be in: YYYY-MM-DD HH:MM:SS format.
0
 
LVL 9

Expert Comment

by:Derek Jensen
ID: 39703296
@kaufmed, I did a bit of research, and as far as I could tell, MySQL does not support native escaped chars, such as \\d, with these 7 exceptions: b t n v f r . But, if I'm understanding this correctly, they must be enclosed within special brackets, [. .] like so:
[[.\n\r.]] -- This matches any newline

Open in new window

It does, however, natively support escaping, such as \\+. I'm not sure on if the special cases above need double backslashing...

A simpler regex (with the same limitation as @matthew pointed out with his regex) might be:

^([0-9]{2,4}-){3} *([[0-9]{1,2}:){2,3}$

I've made some more complex regex before, that would account for any separator in any order, validating any date formatted in any fashion, but that was using PCRE, so...there you go. :-)
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 39703332
@bigdogdman

Your pattern requires a hyphen after the day and a colon after the seconds.
0
 
LVL 9

Expert Comment

by:Derek Jensen
ID: 39703346
Oh, my mistake; good catch, thanks kaufmed. :-)

^([0-9-]{2,5}){3} *([[0-9:]{1,3}){2,3}$

Open in new window

This of course makes it less strict; let me see if I can refactor it:

^([0-9]{2,4}-){2}[0-9] *([[0-9]{1,2}:){1,2}[0-9]$

Open in new window

0
 

Author Comment

by:rgb192
ID: 39705583
okay
 select column from table when/where column REGEXP 'somedatepattern'

is this the proper select
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 39706104
According to the documentation, it should be. Use WHERE not WHEN, though.
0
 
LVL 9

Expert Comment

by:Derek Jensen
ID: 39706370
Personally, I like to use 'RLIKE' simply because there's less chance I misspell it:

SELECT column FROM table WHERE column RLIKE '^([0-9]{2,4}-){2}[0-9] *([[0-9]{1,2}:){1,2}[0-9]$';

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39708804
I would have a look at the built-in date functions.
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_str-to-date

Select column from table where str_to_date(column, '@Y-@m-@d @h:@i:@s') is NOT NULL;
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39711012
I think you're making this a lot more complicated than it needs to be.  You know the names of the columns because you can execute SHOW CREATE TABLE.  You probably know what columns contain DATETIME values, but are miscaptioned VARCHAR.  So the really simple process would go like this.

1. ALTER TABLE to add the new DATETIME column
2. SELECT the key and the miscaptioned column, with each row
A. Use PHP strtotime() to make a UNIX timestamp and date('c') to make the ISO-8601 date string
B. UPDATE the row to set the value in the new DATETIME column
C. Lather, rinse, repeat.

At the end of the process you can check the success by looking for DATETIME values of January 1, 1970 UTC.  These are the rows where the date conversion failed.  You can correct those manually by examining the information from the miscaptioned column.  PHP strtotime() is pretty forgiving (much moreso than REGEX) so there will probably be very few rows where the process fails.

Once you're satisfied with the results, you can probably just rename the columns and your scripts will continue to work correctly, but it would still be a good idea to have the appropriate backups and test cases prepared ahead of time.
0
 

Author Comment

by:rgb192
ID: 39717869
Ray: I wrote that I want to do this without php because php would be more difficult for me to test.  Mysql, I just copy paste into mysql workbench


SELECT column FROM table WHERE column RLIKE '^([0-9]{2,4}-){2}[0-9] *([[0-9]{1,2}:){1,2}[0-9]$';

NO results

Select column from table where str_to_date(column, '@Y-@m-@d @h:@i:@s') is NOT NULL
no results

I did not know the exact query syntax to try the other regexes
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39718019
There is a saying among experienced programmers that goes like this: "I had a problem so I used a regular expression.  Now I have two problems."  The reason I suggested getting PHP strtotime() involved in the process is because it will reduce the number of problems, not increase them.  It's your data base, so you're free to choose any solution that works for you.
0
 
LVL 9

Accepted Solution

by:
Derek Jensen earned 250 total points
ID: 39720034
First off, @Ray is right. @Ray is always right. Just do what he says (if at all possible).

Secondly, you did change column and table to what they're supposed to be, right? Unless you actually have a table named 'table' with a column named 'column', those queries aren't going to work. I'm sorry if this question is insulting; sometimes we have to ask just to make sure.
Pasting your SQL in here exactly as it appears isn't going to compromise your security any either; just don't paste the results in here. ;-)

But, indeed, I tried and I tried (and I Tried and I Tried!!) but I didn't Get no Satisfaction from my regex.
I did, however, get results with this statement using @kaufmed's regex:
SELECT <column_name>
FROM <table_name>
WHERE <column_name>
RLIKE  '^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]$';

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39720144
Please see http://www.laprbass.com/RAY_temp_rgb192.php

You will see that strtotime() is so much more forgiving than REGEX!

Test data is at lines 10-20.  ALTER TABLE is at line 98.  SELECT/UPDATE is at line 138.  Always back up your tables before you try something like this.

<?php // RAY_temp_rgb192.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// SEE http://www.experts-exchange.com/Programming/Languages/Regular_Expressions/Q_28312994.html


// CREATE AN ARRAY OF TEST DATA
$test_dates_arrays = array
( array( "vdate" => "2013-12-04 00:44:52" )
, array( "vdate" => "2013-12-04 00:45:53" )
, array( "vdate" => "2013-12-04 00:46:58" )
, array( "vdate" => "Yesterday" )
, array( "vdate" => "Gooseball" )
, array( "vdate" => "Tomorrow"  )
, array( "vdate" => "Now"  )
, array( "vdate" => " 2013-12-4     00:44:52"  )
, array( "vdate" => " December 17, 2:38pm"  )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, vdate VARCHAR(24) NOT NULL DEFAULT ''
)
"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// LOADING OUR DATA INTO THE TABLE
foreach ($test_dates_arrays as $row)
{
    $safe_dt  = $mysqli->real_escape_string($row['vdate']);
    $sql = "INSERT INTO my_table ( vdate ) VALUES ( '$safe_dt' )";
    if (!$res = $mysqli->query($sql))
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_dt</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// ALTER THE TABLE TO ADD THE DATETIME COLUMN
$sql = "ALTER TABLE my_table ADD COLUMN ddate DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// VISUALIZE THE TABLE STATUS AFTER "ALTER TABLE"
$sql = "SHOW CREATE TABLE my_table";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
while ($row = $res->fetch_object())
{
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;


// GET THE ROWS THAT HAVE EMPTY ddate VALUES
$sql = "SELECT * FROM my_table WHERE ddate = '0000-00-00 00:00:00' ORDER BY id";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO UPDATE THE COLUMNS
while ($row = $res->fetch_object())
{
    // IF THE TIMESTAMP IS OK
    $tst = strtotime($row->vdate);
    if ($tst)
    {
        $new = date('c', $tst);
        $uql = "UPDATE my_table SET ddate = '$new' WHERE id = $row->id LIMIT 1";
        if (!$upd = $mysqli->query($uql))
        {
            $err
            = 'QUERY FAILURE:'
            . ' ERRNO: '
            . $mysqli->errno
            . ' ERROR: '
            . $mysqli->error
            . ' QUERY: '
            . $uql
            ;
            trigger_error($err, E_USER_ERROR);
        }
    }
}
echo PHP_EOL;


// GET ALL THE ROWS TO SHOW WHAT WE HAVE DONE
$sql = "SELECT * FROM my_table ORDER BY id";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
while ($row = $res->fetch_object())
{
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;

Open in new window

0
 

Author Closing Comment

by:rgb192
ID: 39722348
I agree with you: Ray is always right, but i asked for a mysql query because I didnt want to use php because pdo/mysqli, select, update would take me hours.

I tested bigdogdman's query of kaufmed regex in seconds.
0
 

Author Comment

by:rgb192
ID: 39722424
I am not sure if Ray has the correct answer to this question:
because I wrote

what is the regular expression for mysql (not using php) to see if varchar is written like a date format

Ray has the correct answer that will solve my project which Ray has been advising me for months.

So this is the followup.

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28319524.html
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now