Hi, Said! On Nov 30, Said Ramirez wrote:
Hello, I am trying to setup a range partitioned table using 5.5.27-MariaDB-log MariaDB Server using a timestamp field. According to the documents, I should be able to use unix_timestamp(ts) as my partitioning function ( http://dev.mysql.com/doc/refman/5.5/en/partitioning-range.html ) . I find, however, that it is not possible to use unix_timestamp using mariadb because with mariadb the unix_timestamp function returns an floating point value:
mariadb> select unix_timestamp('2012-12-01'); +------------------------------+ | unix_timestamp('2012-12-01') | +------------------------------+ | 1354320000.000000 | +------------------------------+ 1 row in set (0.00 sec)
However the MySQL version of unix_timestamp returns an int value:
myqsl> select unix_timestamp('2012-12-01'); +------------------------------+ | unix_timestamp('2012-12-01') | +------------------------------+ | 1354320000 | +------------------------------+ 1 row in set (0.06 sec)
How have people worked around this issue? What other ways can I partition by a timestamp in MariaDB ? Thanks,
So, let me explain the issue first, then suggest a workaround. Since MariaDB 5.3 timestamps can be specified with a microsecond precision. All functions that work with timestamps were extended too. Usually UNIX_TIMESTAMP returns a value with the same scale as its argument. If you do SELECT UNIX_TIMESTAMP(timestamp_field) FROM table you won't see the decimal point and zeros after it. And you can do CREATE TABLE t1 (a timestamp, KEY (a)) PARTITION BY RANGE (unix_timestamp(a)) (PARTITION p0 VALUES LESS THAN (7), PARTITION p1 VALUES LESS THAN MAXVALUE); this works without any problems. The problem happens when you write VALUES LESS THAN UNIX_TIMESTAMP('2008-10-01 00:00:00') UNIX_TIMESTAMP can only see that it's argument is a string. A string, when converted to a number, has an "unknown" scale - basically, a string in a numeric content is always a float. Not only inside UNIX_TIMESTAMP(). That's why UNIX_TIMESTAMP() has to assume the worst, and return the DECIMAL number with 6 digits after the decimal point. A correct solution will be implemented in 10.0 - where we will have proper temporal literals, DATETIME'2008-10-01 00:00:00' will be really a datetime, not a string. A possible workaround in 5.5 can be to use a cast, like UNIX_TIMESTAMP(CAST('2008-10-01 00:00:00' AS DATETIME)) or CAST(UNIX_TIMESTAMP('2008-10-01 00:00:00') AS UNSIGNED) it will work elsewhere, but you cannot use CAST in the partitioning specifications. I know two workarounds that work: * specify the datetime as an integer: 20081001000000. An integer has no fractional part, and thus UNIX_TIMESTAMP() result won't have it either: MariaDB [test]> select unix_timestamp(20081001000000); +--------------------------------+ | unix_timestamp(20081001000000) | +--------------------------------+ | 1222812000 | +--------------------------------+ 1 row in set (0.00 sec) * alternatively, you can use any function that converts its arguments to ingeters. Basically, it's the same as a CAST, but using an allowed function. For example, integer division: MariaDB [test]> select unix_timestamp('2008-10-01 00:00:00') DIV 1; +---------------------------------------------+ | unix_timestamp('2008-10-01 00:00:00') DIV 1 | +---------------------------------------------+ | 1222812000 | +---------------------------------------------+ Hope this helps. Regards, Sergei