Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to ignore fractional seconds with MySQL 5.6?

Tags:

mysql

MySQL 5.6.4 and up supports fractional seconds for temporal column type. http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

But I want to ignore fractional seconds part for compatibility.

mysql> CREATE TABLE `t1` (`created` datetime(0) NOT NULL);
Query OK, 0 rows affected (0.41 sec)

mysql> INSERT INTO t1 set created = '2013-08-27 05:13:21.999';
mysql> INSERT INTO t1 set created = '2013-08-27 23:59:59.999';


mysql> select * from t1;
+---------------------+
| created             |
+---------------------+
| 2013-08-27 05:13:22 |
| 2013-08-28 00:00:00 |
+---------------------+
2 row in set (0.00 sec)

Expected result is '2013-08-27 05:13:21' and '2013-08-27 23:59:59'.

How do I save same result with previous version of MySQL?

like image 482
shoma Avatar asked Aug 27 '13 05:08

shoma


People also ask

How do I convert seconds to fractions in SQL?

To define a column that includes a fractional seconds part, use the syntax type_name ( fsp ) , where type_name is TIME , DATETIME , or TIMESTAMP , and fsp is the fractional seconds precision. For example: CREATE TABLE t1 (t TIME(3), dt DATETIME(6)); The fsp value, if given, must be in the range 0 to 6.

What stores date and time values with fractional precision of seconds?

MySQL permits fractional seconds for TIME , DATETIME , and TIMESTAMP values, with up to microseconds (6 digits) precision.

What are fractional seconds?

Fractional second is the part of the time that is not an integer. So if you have a time like 12345678.9 the fractional second is 0.9.

What is TIMESTAMP in MySQL?

MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.


1 Answers

Try to create the table as below:

CREATE TABLE t1 (dt DATETIME(6) NOT NULL);

SQL Fiddle

like image 87
7alhashmi Avatar answered Nov 15 '22 07:11

7alhashmi