Advanced Search

Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: testing how MySQL is converting my dates

  1. #1
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,681
    Thanks
    78
    Thanked 89 Times in 87 Posts

    Default testing how MySQL is converting my dates

    I am looking to create a bit of code to test how MySQL converts

    2013,09,19,23,38,44

    to

    2013-09-19 23:38:44

    I can do this with php, but I need the MySQL database to do this, so that I can test out how MySQL converts various formats into YYYY-MM-DD HH:MM:SS.

    I can do this by creating a table and adding a column that is in the datetime format and inserting or updating the date in that table. I want to use the MySQL engine to convert the date without manipulating any stored tables.

    I hope this makes sense. I have done things like this before, but I have forgotten the basic syntax and I have not done this with DATETIME conversions before.
    Last edited by james438; 10-01-2013 at 11:18 PM. Reason: reworded first line for clarity. Other slight rewordings.
    To choose the lesser of two evils is still to choose evil. My personal site

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,681
    Thanks
    78
    Thanked 89 Times in 87 Posts

    Default

    I'm going to stop here for tonight. Here is what I currently have (it doesn't work)

    Code:
    <?php include 'dbconnect.php';
    $query = "SELECT DATETIME(20070523091528);";
    $test  = mysqli_query($conn,$query);
    $test1 = mysqli_fetch_array($test,MYSQL_ASSOC);
    $test2 = array_values($test1);
    $test3 = $test2[0];
    echo "$test3 months.";
    ?>
    Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/content/17/8557417/html/animeviews/test.php on line 4

    Warning: array_values() expects parameter 1 to be array, null given in /home/content/17/8557417/html/animeviews/test.php on line 5
    months
    Here is a little reference page on how MySQL converts information into the correct format: ref
    To choose the lesser of two evils is still to choose evil. My personal site

  3. #3
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,624
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Quote Originally Posted by james438 View Post
    I am looking to create a bit of code to convert "2013,09,19,23,38,44" to "2013-09-19 23:38:44" ...
    Actually, MySQL is very lax about datetime formats. Assuming the column you're using is the DATETIME type, you can just throw it in there and it will work:
    Code:
    mysql> USE some_database;
    mysql> CREATE TEMPORARY TABLE dt( dt DATETIME );
    mysql> INSERT INTO dt( dt ) VALUES( '2013,09,19,23,38,44' );
    mysql> SELECT dt FROM dt;
    
    +---------------------+
    | dt                  |
    +---------------------+
    | 2013-09-19 23:38:44 |
    +---------------------+
    1 row in set (0.00 sec)
    The rules are a bit arbitrary and frustrating, but basically, the delimiter you choose doesn't matter as long as you have proper numbers in the proper order (YYYY MM DD hh mm ss).
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  4. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,624
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Sorry; I posted before I saw your second post. The mysql function you're looking for is DATE_FORMAT().

    Code:
    mysql> select DATE_FORMAT( '20130919220255','%Y-%m-%d %T' ) formatted_date;
    
    +---------------------+
    | formatted_date      |
    +---------------------+
    | 2013-09-19 22:02:55 |
    +---------------------+
    1 row in set (0.00 sec)
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  5. The Following User Says Thank You to traq For This Useful Post:

    james438 (09-30-2013)

  6. #5
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,681
    Thanks
    78
    Thanked 89 Times in 87 Posts

    Default

    How would this look if I were to insert what you posted into a php file for execution?
    To choose the lesser of two evils is still to choose evil. My personal site

  7. #6
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,681
    Thanks
    78
    Thanked 89 Times in 87 Posts

    Default

    Sorry, I was really tired last night and wasn't thinking clearly. Here is what I am currently playing with:

    PHP Code:
    <?php include 'dbconnect.php';
    $query "SELECT DATE_FORMAT('20130919220255','%Y-%m-%d %T')";
    $test=mysqli_query($connect,$query);
    $test1=mysqli_fetch_array($testMYSQLI_NUM);
    print 
    $test1[0];
    ?>
    Thank you for your help

    I'm familiar with php and MySQL's documentation on date and time formatting as well as the method it uses to automatically format the date into the YYYY-MM-DD HH:MM:SS format, but I like to confirm their documentation on my end. I also wanted to do this using the MySQL engine without manipulating stored data.
    Last edited by james438; 09-30-2013 at 02:18 PM.
    To choose the lesser of two evils is still to choose evil. My personal site

  8. #7
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,624
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    welcome!
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  9. #8
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,681
    Thanks
    78
    Thanked 89 Times in 87 Posts

    Default

    After refining it a little bit more here is what I have:

    PHP Code:
    <?php include 'dbconnect.php';
    $query "SELECT TIMESTAMP('20130919220255')";
    $test=mysqli_query($connect,$query);
    $test1=mysqli_fetch_array($testMYSQLI_NUM);
    print 
    $test1[0];
    ?>
    TIMESTAMP converts the date string into the DATETIME format. Supposedly TIMESTAMP and DATETIME behave virtually the same if not exactly the same. The odd thing is that TIME, DATE, and TIMESTAMP work, but DATETIME does not. Any idea why this may be?

    One more question. How can I create an array of TIMESTAMP values such as

    PHP Code:
    $query "SELECT TIMESTAMP('20130919220255','20100919220251')"
    or
    PHP Code:
    $query "SELECT TIMESTAMP('20130919220255' AND '20100919220251')"
    I can do this with php to generate several different queries, but I am wondering if this can be done with just one query.
    To choose the lesser of two evils is still to choose evil. My personal site

  10. #9
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,624
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Quote Originally Posted by james438 View Post
    TIMESTAMP converts the date string into the DATETIME format. Supposedly TIMESTAMP and DATETIME behave virtually the same if not exactly the same. The odd thing is that TIME, DATE, and TIMESTAMP work, but DATETIME does not. Any idea why this may be?
    Because TIME, DATE, and TIMESTAMP are functions; DATETIME is a data type. ("DATE", "TIME", and "TIMESTAMP" are also used as the names of data types, which is probably where a lot of the confusion is coming from.)

    Quote Originally Posted by james438 View Post
    One more question. How can I create an array of TIMESTAMP values
    Just make a list of them (if any two items have/may have the same value in their argument, you'll need the aliases. If all items will be unique, there's technically no need).
    Code:
    SELECT TIMESTAMP('20130919220255') ts_1, TIMESTAMP('20100919220251') ts_2;
    
    +---------------------+---------------------+
    | ts_1                | ts_2                |
    +---------------------+---------------------+
    | 2013-09-19 22:02:55 | 2010-09-19 22:02:51 |
    +---------------------+---------------------+
    1 row in set (0.00 sec)
    Of course, there is no such thing as an "array" in MySQL, but mysqli_fetch_array will give you one from the result set.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  11. The Following User Says Thank You to traq For This Useful Post:

    james438 (10-01-2013)

  12. #10
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,681
    Thanks
    78
    Thanked 89 Times in 87 Posts

    Default

    PHP Code:
    <?php include 'dbconnect.php';
    $query "SELECT TIMESTAMP('20130919220255'), TIMESTAMP('20100919220251')";
    $test=mysqli_query($connect,$query);
    $test1=mysqli_fetch_array($testMYSQLI_NUM);
    print_r ($test1);
    ?>
    Ah, yes. MySQL has no form of shorthand except for = and !=. Thank you for explaining that DATETIME is actually a data type and where my confusion was likely coming from.

    Is ts_1 and ts_2 the aliases? I can see how they work and in php I would call them keys. I think that is the last question I have about this. The above code is given above for those that may be curious how it would look in a php file for execution.
    To choose the lesser of two evils is still to choose evil. My personal site

Similar Threads

  1. Saving Dates in mysql
    By d-machine in forum PHP
    Replies: 2
    Last Post: 09-29-2013, 08:02 PM
  2. Converting mysql time stamp into php
    By gurmeet in forum MySQL and other databases
    Replies: 5
    Last Post: 11-06-2009, 01:16 AM
  3. strtotime - Converting Dates
    By nikomou in forum PHP
    Replies: 1
    Last Post: 04-04-2009, 11:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •