Selecting Timestamps

[former_SQLA_member1694898]

I am running ASA 7 and using ISQL to select all fields from a table using a simple select clause. After I get the results I write them to file using the OUTPUT command. One of the columns I am selecting is a timestamp type with a default value of CURRENT_TIMESTAMP. All values get added to the DB using DEFAULT in the INSERT statement. This timestamp column is a key part of the primary key.

My problem arises when I try and reload these records into a new DB. I am getting a -193 error (primary key not unique) due to the fact that the timestamp values are identical in some cases. For instance, I am seeing 2 records that in the reload file containing a timestamp that looks like:

2010-04-12 14:21:36.234

I am wondering how the original DB ever was able to add these two records due to the fact that the timestamp is also the primary key in that DB. I'm thinking it may be a precision thing whereby ISQL is chopping off the miliseconds part of the timestamp and it should be something like:

2010-04-12 14:21:36.23450 (note the extra 2 digits)

Is this possible? Is there a way to select them out of the DB with more precision so I don't encounter the -193 on the INPUT statement?

Thanks!!!!

[Former Member]

It's entirely possible that's what you're seeing (i.e. the timestamp is being truncated on output)

Have a look at the timestamp_format option to see what alternatives are available.

[VolkerBarth]

...and use

select connection_property('timestamp_format')

to find out the current setting in the old database.

[former_SQLA_member1694898]

I set the property to use ss.sssss (all 5 on the milliseconds) but it did not affect the output.

[VolkerBarth]

That's unusual, AFAIK.

Make sure you set this for the appropriate level (i.e. current connection, PUBLIC group, whatsoever).

I would try the above select ... in DBISQL (just before doing the outptu) to make sure the option is set as wanted.

[Former Member]

What API are you using from your application to connect to the database?