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

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?


  • Check if its not timestemp_tz data type
  • Can you also verify that the $TZ variable is set correctly across all the nodes in your cluster?
  • The dates are defined as follows.

       date_record_added TIMESTAMP WITHOUT TIMEZONE,
       date_last_modified TIMESTAMP WITHOUT TIMEZONE,

  • 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.
  • The dates are defined as follows.

       date_record_added TIMESTAMP WITHOUT TIMEZONE,
       date_last_modified TIMESTAMP WITHOUT TIMEZONE,
  • 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.


  •    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) {
                        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();

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file