# mysqldump or export to csv  in a different timestamp format

**URL:** <https://forums.percona.com/t/mysqldump-or-export-to-csv-in-a-different-timestamp-format/8185>\
**Category:** Percona XtraDB Cluster 5.x\
**Created:** [October 23, 2020, 4:28pm UTC](https://forums.percona.com/t/mysqldump-or-export-to-csv-in-a-different-timestamp-format/8185 "2020-10-23T16:28:38Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Rkot](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rkot/32/70_2.png) [@Rkot](https://forums.percona.com/u/Rkot)\
**Post date:** [October 23, 2020, 4:28pm UTC](https://forums.percona.com/t/mysqldump-or-export-to-csv-in-a-different-timestamp-format/8185/1 "2020-10-23T16:28:38Z")

</div>

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 \*’

---

<div class="post-metadata">

**Author:** ![yves.trudeau](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/yves.trudeau/32/2825_2.png) [@yves.trudeau](https://forums.percona.com/u/yves.trudeau)\
**Post date:** [October 29, 2020, 11:06am UTC](https://forums.percona.com/t/mysqldump-or-export-to-csv-in-a-different-timestamp-format/8185/2 "2020-10-29T11:06:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Rkot](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rkot/32/70_2.png) [@Rkot](https://forums.percona.com/u/Rkot)\
**Post date:** [October 29, 2020, 6:20pm UTC](https://forums.percona.com/t/mysqldump-or-export-to-csv-in-a-different-timestamp-format/8185/3 "2020-10-29T18:20:50Z")

</div>

Thank you so much, it worked

---

<div class="post-metadata">

**Author:** ![Rkot](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rkot/32/70_2.png) [@Rkot](https://forums.percona.com/u/Rkot)\
**Post date:** [November 18, 2020, 3:33pm UTC](https://forums.percona.com/t/mysqldump-or-export-to-csv-in-a-different-timestamp-format/8185/4 "2020-11-18T15:33:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![yves.trudeau](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/yves.trudeau/32/2825_2.png) [@yves.trudeau](https://forums.percona.com/u/yves.trudeau)\
**Post date:** [November 23, 2020, 8:16am UTC](https://forums.percona.com/t/mysqldump-or-export-to-csv-in-a-different-timestamp-format/8185/5 "2020-11-23T08:16:34Z")

</div>

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.