Convert timestamp to tick

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.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )