• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1912
  • Last Modified:

regular expression to see if mysql is a date

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
rgb192
Asked:
rgb192
  • 4
  • 4
  • 3
  • +3
2 Solutions
 
käµfm³d 👽Commented:
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
 
Matthew KellyCommented:
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
 
Derek JensenCommented:
@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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
käµfm³d 👽Commented:
@bigdogdman

Your pattern requires a hyphen after the day and a colon after the seconds.
0
 
Derek JensenCommented:
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
 
rgb192Author Commented:
okay
 select column from table when/where column REGEXP 'somedatepattern'

is this the proper select
0
 
käµfm³d 👽Commented:
According to the documentation, it should be. Use WHERE not WHEN, though.
0
 
Derek JensenCommented:
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
 
Daniel WilsonCommented:
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
 
Ray PaseurCommented:
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
 
rgb192Author Commented:
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
 
Ray PaseurCommented:
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
 
Derek JensenCommented:
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
 
Ray PaseurCommented:
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
 
rgb192Author Commented:
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
 
rgb192Author Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now