testing how MySQL is converting my dates

[james438]

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.

[james438]

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


<?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 (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-literals.html)

[traq]

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:
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).

[traq]

Sorry; I posted before I saw your second post. The mysql function you're looking for is DATE_FORMAT() (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format).


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)

[james438]

How would this look if I were to insert what you posted into a php file for execution?

[james438]

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


<?php include 'dbconnect.php';
$query = "SELECT DATE_FORMAT('20130919220255','%Y-%m-%d %T')";
$test=mysqli_query($connect,$query);
$test1=mysqli_fetch_array($test, MYSQLI_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.

[traq]

welcome!

[james438]

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


<?php include 'dbconnect.php';
$query = "SELECT TIMESTAMP('20130919220255')";
$test=mysqli_query($connect,$query);
$test1=mysqli_fetch_array($test, MYSQLI_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


$query = "SELECT TIMESTAMP('20130919220255','20100919220251')";
or

$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.

[traq]

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.)


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).

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.

[james438]

<?php include 'dbconnect.php';
$query = "SELECT TIMESTAMP('20130919220255'), TIMESTAMP('20100919220251')";
$test=mysqli_query($connect,$query);
$test1=mysqli_fetch_array($test, MYSQLI_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.

[traq]

Yup; but don't call them "keys" in MySQL. ;)