How to convert Mysql timestamp to sysdate (6) format (with milliseconds)?

Question

I am new to postgres and sometimes I worked on Mysql. I need to transfer content from a Mysql table to a Postgres table.

My Postgres table is as follows:

Column | Type | Modifiers --------------------------------+-----------------------------+------------------------------------------------------------------------------------------------ id | integer | created_at | timestamp without time zone | not null default timezone('UTC'::text, now()) updated_at | timestamp without time zone | not null default timezone('UTC'::text, now()) key_classification | character varying(2000) | not null 

I am inserting the value of created_at and updated_at from the mysql table, which is in the form "2014-09-04 23:40:14".

when I insert a row into my postgres table, the default timestamp is of the form "2016-01-22 17: 44: 53.342757", which includes the second timestamp in the timestamp.

Now I need to add a millisecond to the mysql timestamp format to match the postgres table format.

Help add a millisecond to the timestamp.

Thanks in advance!

Answer 1

Morning Arunraj Hope this helps?

I am running Mysql / MariaDB

 MariaDB [(none)]> SHOW VARIABLES LIKE "%version%"; +-------------------------+-----------------+ | Variable_name | Value | +-------------------------+-----------------+ | innodb_version | 5.6.25-73.1 | | protocol_version | 10 | | slave_type_conversions | | | version | 10.0.21-MariaDB | | version_comment | MariaDB Server | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_malloc_library | system | +-------------------------+-----------------+ 8 rows in set (0.00 sec) 

And I refer to 11.3.6 Fractional seconds in temporary values What is mysql version 5.7

To run an example

 As mysql Admin mysql -u USERNAME -p CREATE USER 'test'@'localhost' IDENTIFIED BY 'test'; CREATE DATABASE test; GRANT ALL ON test.* TO 'test'@'localhost'; 

Finish and then log in as a test

 mysql -u test -p 

Password is a test

Then

 CREATE TABLE test.td6(ts6 timestamp(6)); INSERT INTO test.td6 VALUES ('2016-01-22 17:44:53.342757'); 

The value entered is the timestamp format you want to insert into mysql.

All the best