column is of type timestamp without time zone but expression is of type integer

Question

I get error message like below , when I create this table just like others set column timestamp timestamp without time zone NOT NULL , but when I tried to insert 2015-08-16 18:51:05 to this table, then I get error, but other table is work, why and how to solve it?

CREATE TABLE IF NOT EXISTS "UserForgetPasswordPending"(
"UserForgetPasswordPendingId" SERIAL NOT NULL,
"Email" varchar(50) NOT NULL,
"TokenTimestamp" timestamp without time zone NOT NULL,
"Token" varchar(100) NOT NULL,
PRIMARY KEY ("UserForgetPasswordPendingId")
);
ALTER TABLE "UserForgetPasswordPending"
  OWNER TO db_admin;

error

[error: column "TokenTimestamp" is of type timestamp without time zone but expression is of type integer]
  name: 'error',
  length: 216,
  severity: 'ERROR',
  code: '42804',
  detail: undefined,
  hint: 'You will need to rewrite or cast the expression.',

insert

var utc = moment(new Date()).unix();
var tokenTimestamp = moment.unix(utc).format('YYYY-MM-DD HH:mm:ss');

var upsertUserForgetPasswordPending = function(userEmail, tokenTimestamp, token) {
  return new Promise(function (fulfill, reject){
    var queryInsert = 'INSERT INTO "UserForgetPasswordPending" ("Email","TokenTimestamp","Token") SELECT $1,2,$3';
    var queryUpsert = 'UPDATE "UserForgetPasswordPending" SET "TokenTimestamp" = $2, "Token" = $3 WHERE "Email" = $1';
    var query = 'WITH upsert AS ('+queryUpsert+' RETURNING *) '+queryInsert+' WHERE NOT EXISTS (SELECT * FROM upsert)';
console.log(tokenTimestamp);
    dbClient.query(query, [userEmail,tokenTimestamp,token], function(error, result) {
      if (error) {
        reject(error);
      } else {
        fulfill(result);
      }
    });
  });
};

Solution

This because you are inserting integer data to time stamp column.

Correct the following syntax:

var queryInsert = 'INSERT INTO "UserForgetPasswordPending ("Email","TokenTimestamp","Token") SELECT $1,2,$3';

In above query you are selecting 2 for TokenTimestamp that's why you are getting this error.

you should replace 2 with some date time format yyyy-mm-dd hh:mm:ss .

For example: '2015-08-07 05:00:01'

Comments

[IMSoP]

I think it's even simpler: it was supposed to say $2, i.e. the second parameter to the prepared statement, as seen in the UPDATE query.

[sujithramanathan]

Please note date time format is case sensitive use yyyy-MM-dd hh:mm:ss instead of yyyy-mm-dd hh:mm:ss