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

Question

I am new to postgres and I have been working on Mysql for quiet sometimes. I need to migrate content from Mysql table to Postgres table.

My Postgres table is like this:

             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 created_at and updated_at value from 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 in the form "2016-01-22 17:44:53.342757" which inclues millisecond in the timestamp.

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

Please help to add the millisecond into the timestamp.

Thanks in advance!

Maybe you need to read [Timestamp with a millisecond presicion how to save the in mysql](http://stackoverflow.com/questions/26299149/timestamp-with-a-millisecond-precision-how-to-save-them-in-mysql) — eusoj, Jan 26 '16 at 15:39
do you need this precision in mysql, or do you just think you need it for the migration? postgresql will happily accept also shorter timestamps, even "2016-01-22" as shorthand for "2016-01-22 00:00:00.000000" — knitti, Feb 18 '16 at 15:35

score 0 · Answer 1 · answered Mar 10 '16 at 10:28

Morning Arunraj I hope this helps ?

I'm 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'm referencing 11.3.6 Fractional Seconds in Time Values Which is mysql version 5.7

To run the 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';

Quit and then login as test

mysql -u test -p

The password is test

Then

CREATE TABLE test.td6(ts6 timestamp(6));

INSERT INTO test.td6 VALUES ('2016-01-22 17:44:53.342757');

The inserted value is the timestamp format you wanted to insert into mysql.

All the best

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

1 Answers1