Start a new topic

Export timestamp in JDBC escape syntax

[This topic is migrated from our old forums. The original author name has been removed]

The export GUI and @export command writes timestamp columns in the SQL output format as: e.g TIMESTAMP '2010-05-12 00:32:27' The formatting of the string can be configured but there is no obvious option to use JDBC escape syntax: e.g. {ts 'yyyy-mm-dd hh:mm:ss.f...'} How can this be done? other: I'd like to optionally suppress the word TIMESTAMP.
1 Comment

[This reply is migrated from our old forums.]

Re: Export timestamp in JDBC escape syntax
Hi Jason, I've already answered you via email, but for information to others, I answer here as well. I'm surprised no one has asked about this before, but it is currently not possible to get rid of 'TIMESTAMP' (and similar for other types and other databases) when using @export or Export Table to create an SQL script. The date/time values in INSERT statements are formatted to work when running the script with the same database type. This obviously does not work well when running it with another database type. When this code was written, support for the JDBC escape syntax was spotty at best but things may have improved. I totally agree that there should at least be a "JDBC escape syntax" choice, and maybe even support for any format. The Export Grid wizard (launched from the right-click menu in the Data tab or a Result Set tab) allows you to remove 'TIMESTAMP' and use any prefix/suffix you want, or none at all. For instance, you can get it formatted with JDBC escape syntax. The drawback is that using it means first loading all data into the GUI, which may not be an option if the table is large? Another approach is to export/import using the CSV format. No additional prefix/suffix is used when formatting date/time data. I have opened a ticket to look into this area again. Until then, I'm afraid you need to change the formatting in the script using an editor or grep or similar. Best Regards, Hans