• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Convert timestamp to tick

#21
Did some more research on this on MySQL web site, it seems that you need
to create the TIMESTAMP field like below to add the milliseconds to it.

PHP Code:
CREATE TABLE t1 (
 
 ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6ON UPDATE CURRENT_TIMESTAMP(6)
); 


And then to get it back use a select like below.

PHP Code:
mysqlSELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');
 
       -> 1447431619.012 

Hope that helps anyone for adding milliseconds to a TIMESTAMP

OK, Did a MySQL table test using TIMESTAMP and DATETIME column fields.

And it does add the milliseconds to the fields.

Database code below.

PHP Code:
-- --------------------------------------------------------
--
-- 
Table structure for table ``
--
-- --------------------------------------------------------

DROP TABLE IF EXISTS `stamp_test`;
CREATE TABLE IF NOT EXISTS `stamp_test` (
    `
id        INT(11   UNSIGNED NOT NULL  AUTO_INCREMENT,
    `
deleted   TINYINT(1UNSIGNED NOT NULL  DEFAULT '0',
    `
tsc_at    TIMESTAMP(6                 DEFAULT CURRENT_TIMESTAMP(6),
    `
tsu_at    TIMESTAMP(6                 DEFAULT CURRENT_TIMESTAMP(6ON UPDATE CURRENT_TIMESTAMP(6),
    `
created_atDATETIME(6                  DEFAULT CURRENT_TIMESTAMP(6),
    `
updated_atDATETIME(6                  DEFAULT CURRENT_TIMESTAMP(6ON UPDATE CURRENT_TIMESTAMP(6),
    
PRIMARY KEY (`id`)
ENGINE InnoDB DEFAULT CHARSET utf8 COLLATE utf8_unicode_ci;

-- --------------------------------------------------------
--
-- 
Dumping data for table `stamp_test`
--
INSERT INTO `stamp_testVALUES
    
('1''0'now(6), now(6), now(6), now(6)); 

If you notice you need to specify the milliseconds field length in the fields even NOW(6).

Here is a method to strip out the un-wanted characters:

PHP Code:
   /**
     * getTicks ()
     * -------------------------------------------------------------------
     *
     * Method to flatten TIMESTAMP & DATETIME fields from MySQL
     *
     * EXAMPLE:
     *
     * string(26) "2018-03-13 06:45:21.725098"
     * 
     * getTicks(string $data);
     * string(20) "20180313064521725098"
     * 
     * You would need a BIGINT(20) to store this in MySQL
     * 
     * @param  string $data
     * @return string
     */
    
private function getTicks(string $data) : string
    
{
 
       $replace = ['-''.'' '':'];

 
       return (is_string($data)) ? str_replace($replace""$data) : '';
 
   

To Store in MySQL you would need to use a BIGINT(20) field value.
What did you Try? What did you Get? What did you Expect?

Joined the CodeIgniter Community in 2009.          ( Skype: insitfx )
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2018 MyBB Group.