CODEWITHSUNDEEP
mysql
Dhileepan
Dhileepan

Reputation: 2047

Use timestamp in Range Partition at Mysql

I was tried to make partition by the timestamp in mysql table. But it is returning an error. If I try with NOW() also returning an error. How resolve this error and make partition with timestamp datatype ?

  CREATE TABLE tblemployeepunch (
  fld_id int(11) NOT NULL AUTO_INCREMENT,
  fld_date Varchar(15) DEFAULT NULL,
  fld_rawpunchdate varchar(25) DEFAULT NULL,
  fld_rawpunchtime varchar(25) DEFAULT NULL,
  fld_cardno varchar(50) DEFAULT NULL,
  fld_reasoncard varchar(20) DEFAULT NULL,
  fld_mode varchar(20) DEFAULT NULL,
  fld_punchdatetime varchar(50) DEFAULT NULL,
  fld_crtdate  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY fld_id (fld_id,fld_crtdate),
  KEY in_timesheet (fld_cardno,fld_punchdatetime,fld_mode,fld_rawpunchtime),
  KEY in_emppunch (fld_cardno,fld_rawpunchdate,fld_punchdatetime)
  )
  PARTITION BY RANGE (fld_crtdate)
  (PARTITION p_Sep12 VALUES LESS THAN (TO_TIMESTAMP('2012-10-01 00:00:00')),
  PARTITION p_Oct12 VALUES LESS THAN (TO_TIMESTAMP('2012-11-01 00:00:00')),
  PARTITION p_Nov12 VALUES LESS THAN (TO_TIMESTAMP('2012-12-01 00:00:00')),
  PARTITION p_Max VALUES LESS THAN MAXVALUE );

Upvotes: 0

Views: 2062

Answers (1)

Dhileepan
Dhileepan

Reputation: 2047

This is the correct query to solve this issue. Use UNIX_TIMESTAMP 

      CREATE TABLE tblemployeepunch (
      fld_id int(11) NOT NULL AUTO_INCREMENT,
      fld_date Varchar(15) DEFAULT NULL,
      fld_rawpunchdate varchar(25) DEFAULT NULL,
      fld_rawpunchtime varchar(25) DEFAULT NULL,
      fld_cardno varchar(50) DEFAULT NULL,
      fld_reasoncard varchar(20) DEFAULT NULL,
      fld_mode varchar(20) DEFAULT NULL,
      fld_punchdatetime varchar(50) DEFAULT NULL,
      fld_crtdate  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY fld_id (fld_id,fld_crtdate),
      KEY in_timesheet (fld_cardno,fld_punchdatetime,fld_mode,fld_rawpunchtime),
      KEY in_emppunch (fld_cardno,fld_rawpunchdate,fld_punchdatetime)
    )
     PARTITION BY RANGE (UNIX_TIMESTAMP(fld_crtdate))
     (PARTITION p_Sep12 VALUES LESS THAN (UNIX_TIMESTAMP('2012-10-01 00:00:00')),
     PARTITION p_Oct12 VALUES LESS THAN (UNIX_TIMESTAMP('2012-11-01 00:00:00')),
     PARTITION p_Nov12 VALUES LESS THAN (UNIX_TIMESTAMP('2012-12-01 00:00:00')),
     PARTITION p_Max VALUES LESS THAN MAXVALUE );

Upvotes: 1

Related Questions