maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxxx>]

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