Convert Text to PostgreSQL Time-Format=HH:mm:ss

1

Hello,
I get an error during database export with data type time
Value: 00:28:50.07 (0 hours:28minutes:50.07 seconds)
I get an error during database export.
The seconds without decimal, i.e. 00:28:50 in the format HH:mm:ss, also does not work.

image
image1055×427 86.8 KB

How do I get the value stored in the database?

Thank you for your effort:

Regards,

Adrian

2

According to your screenshot, values in field [tdauer] are text. While the database expects them to be time. In EasyMorph, date/time is a number. So you need to convert [tdauer] into a numeric date/time.

When EasyMorph converts a text date into a number date and the text date has only the time part but has no day part, EasyMorph assumes the day is today. I suspect, in your case you need to subtract the today’s date, e.g.

date([tdauer], 'HH:mm:ss') - today()

image

3

Hello Adrian,

The “Bulk export to database” action can’t export values to time columns.

You can use the “Export to database” action to export time values in the text form. Like 12:34:56 or 12:34:56.123456.

4

Hi Dmitry,
thanks for feedback, nice idea for the date format, but in the PostgreSQL database the data type of the field is time.

regards,

Adrian

5

Hi Andrew,
unfortunately it doesn’t work with “Export to Database” either.
… and there is no Customformat-Option in Metadata, this would also be nice :wink:

regards,
Adrian

image
image1221×942 141 KB

6

Hi Adrian,

It seems that you are trying to insert an EasyMorph date/time value, which is stored as a number. That is not supported by the Export to database action.

Instead, you should try to export your time values in their initial form - as text. So you don’t have to use the Convert data types action or the date([tdauer], 'HH:mm:ss') - today() expression.

7

Hi Andrew,
that’s what I tried too.
Then postgreSQL complains because the value is interpreted as text.

Date-Columns have the same problem.

The workaround is:

  1. Alter Table (Field as Text)
  2. BULK Insert
  3. Alter Table (Field as date)

So the PostgreSQL switch the value as date.

Regards,

Adrian

8

Hi Adrian,

The “Export to database” action, in contrast to the “Bulk export to database” action, should not complain about text values:

image

Please make sure that the exported values are actual text values and not formatted numeric values.