+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Convert timestamp to tick (/showthread.php?tid=70200)
RE: Convert timestamp to tick - InsiteFX - 03-13-2018
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(6) ON UPDATE CURRENT_TIMESTAMP(6)
);
And then to get it back use a select like below.
PHP Code:
mysql> SELECT 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(1) UNSIGNED NOT NULL DEFAULT '0',
`tsc_at` TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
`tsu_at` TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`created_at` DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
`updated_at` DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON 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_test` VALUES
('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.