mysqldump or export to csv in a different timestamp format

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi

I have a Percona Xtradb cluster 5.7 with many tables with timestamp column/columns. I need to export the data of all the tables into a csv or mysqldump file . The problem i am facing is that the output file needs to be in a different timestamp format as below

2020-01-22 19:24:27.953 (current)-----------------> 22-JAN-2020 7:24:27.953 PM ( desired)

Is there any way i can set any variable at session level to take a dump of the data ?

The problem with using DATE_FORMAT is that, i need to set it for each timestamp column of the table . Also I will need to define the columns explicitly instead of using ‘SELECT *’

2

What about something like this:

select concat('SELECT ‘,group_concat(COLUMN_NAME order by ORDINAL_POSITION),’ FROM ',TABLE_NAME) as statement

from (select ORDINAL_POSITION, table_schema, table_name, if(DATA_TYPE=‘timestamp’,concat(‘DATE_FORMAT(CONVERT_TZ(’,COLUMN_NAME,‘,“GMT”,“EST”),“%e-%b-…”)’),COLUMN_NAME) as COLUMN_NAME from COLUMNS order by table_schema, table_name, ORDINAL_POSITION) st

where TABLE_SCHEMA=‘sysbench’ and TABLE_NAME = ‘sbtest%’ group by table_name;

±-----------------------------------------------------------------------------------+

| statement |

±-----------------------------------------------------------------------------------+

| SELECT id,k,c,pad,DATE_FORMAT(CONVERT_TZ(ts,“GMT”,“EST”),“%e-%b-…”) FROM sbtest1 |

±-----------------------------------------------------------------------------------+

Adjust the date format and the final where clause of course. I added a timestamp column to the sbtest1 table to illustrate.

3

Thank you so much, it worked

4

I need to add the format for DATE datatype too , when i tried to use ELSEIF for the above quey , it gives me syntax error. Can you please suggest how to incorporate the date datatype also ? I tried as below -

if(DATA_TYPE=‘timestamp’ then concat(‘DATE_FORMAT(’,COLUMN_NAME,‘,“%d-%b-%y %l.%i.%s %p”)’),COLUMN_NAME) as COLUMN_NAME

elseif(DATA_TYPE=‘date’ then concat(‘DATE_FORMAT(’,COLUMN_NAME,‘,“%d-%b-%y”)’),COLUMN_NAME) as COLUMN_NAME) as COLUMN_NAME

end if

5

Hi,

personally I would merge the IF statements together:

if(DATA_TYPE=‘timestamp’ then concat(‘DATE_FORMAT(’,COLUMN_NAME,‘,“%d-%b-%y %l.%i.%s %p”)’), if(DATA_TYPE=‘date’ then concat(‘DATE_FORMAT(’,COLUMN_NAME,‘,“%d-%b-%y”)’),COLUMN_NAME)) as COLUMN_NAME

I hope I have the parenthese right, the above is untested.