Bug in Vertica for timestamp insertion? Incorrect timestamp is off by one hour.

Deklan_Dieterly

Seeing columns declared as Timestamp having incorrect timestamp inserted. The hour is off by one from the original data. For example, 2013-03-09 20:20:14 is being inserted as 2013-03-09 19:20:14 Anyone else see this or know of a bug filed for this?

eli_revach

Check if its not timestemp_tz data type

Curtis_1

Can you also verify that the $TZ variable is set correctly across all the nodes in your cluster?

Deklan_Dieterly

The dates are defined as follows.

   date_record_added TIMESTAMP WITHOUT TIMEZONE,
   date_last_modified TIMESTAMP WITHOUT TIMEZONE,

Deklan_Dieterly

We can reproduce this on a single node using the Community Edition of Vertica.  The timezone on the node is set to UTC0.  We are using JDBC with batches.  This does not happen  for all rows inserted.  We have observed it to happen when we insert more than 5K rows at a time.

Deklan_Dieterly

The dates are defined as follows.

   date_record_added TIMESTAMP WITHOUT TIMEZONE,
   date_last_modified TIMESTAMP WITHOUT TIMEZONE,

[Deleted User]

Hi Deklan, 
As I can see in our systems there is not reported bug reported for the time zone, however, in the way that you mention it could be one.

Are you a Enterprise user that have access to support? If so can you please open a support ticket so they do the appropriate research and get to the root cause of this issue. If not can you please send the exact statements that you do to reproduce it.

Thanks, 

Eugenia

Deklan_Dieterly

   protected PreparedBatch initializeBatch() {
        return handle.prepareBatch("insert into CS.Provisionings values (:status, :template_id, :project_id, :service_account_id," +
                ":date_record_added, :date_last_modified, :modified_by, :batch_timestamp, :priorStatus)");
    }

    protected void prepareBatch() {
        for (Provisioning provisioning : toCommit) {
            batch.add().
                    bind(Provisioning.STATUS, provisioning.getStatus()).
                    bind(Provisioning.TEMPLATE_ID, provisioning.getTemplateId()).
                    bind(Provisioning.PROJECT_ID, provisioning.getProjectId()).
                    bind(Provisioning.SERVICE_ACCOUNT_ID, provisioning.getServiceAccountId()).
                    bind(Provisioning.DATE_RECORD_ADDED, provisioning.getDateRecordAdded()).
                    bind(Provisioning.DATE_LAST_MODIFIED, provisioning.getDateLastModified()).
                    bind(Provisioning.MODIFIED_BY, provisioning.getModifiedBy()).
                    bind("batch_timestamp", provisioning.getBatchTimestamp()).
                    bind(Provisioning.PRIOR_STATUS, provisioning.getPriorStatus());
        }
    }


batch = initializeBatch();
prepareBatch();
batch.execute();


Dates in bold are off by one hour when inserted into the DB.  Dates are of the format, '2013-03-09 20:20:14'.

This happens on several tables using basically the same code structure as shown above except the column names are different.