postgresql 列是没有时区的时间戳类型，但表达式是整数类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32034274/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
column is of type timestamp without time zone but expression is of type integer
提问by user1775888
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:05to this table, then I get error, but other table is work, why and how to solve it?
我收到如下错误消息，当我创建这个表时，就像其他人设置列时间戳一样timestamp without time zone NOT NULL，但是当我尝试插入2015-08-16 18:51:05到这个表时，我得到错误，但其他表可以工作，为什么以及如何解决它？
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 ,2,';
var queryUpsert = 'UPDATE "UserForgetPasswordPending" SET "TokenTimestamp" = , "Token" = WHERE "Email" = ';
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);
}
});
});
};
回答by Abhishek
This because you are inserting integer data to time stampcolumn.
这是因为您将整数数据插入到time stamp列中。
Correct the following syntax:
更正以下语法：
var queryInsert = 'INSERT INTO "UserForgetPasswordPending ("Email","TokenTimestamp","Token") SELECT $1,2,$3';
var queryInsert = 'INSERT INTO "UserForgetPasswordPending ("Email","TokenTimestamp","Token") SELECT $1,2,$3';
In above query you are selecting 2for TokenTimestampthat's why you are getting this error.
在上面的查询您所选择2的TokenTimestamp，这就是为什么你得到这个错误。
you should replace 2with some date time format yyyy-mm-dd hh:mm:ss.
你应该2用一些日期时间格式替换yyyy-mm-dd hh:mm:ss。
For example: '2015-08-07 05:00:01'
例如： '2015-08-07 05:00:01'
回答by subodhkarwa
For me casting the type explicitely worked. Please note, I used joda Datetime
对我来说，明确地投射类型有效。请注意，我使用了 joda Datetime
@CreatedDate
@Column(name = "created_on", updatable = false, nullable = false)
@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime")
private DateTime createdOn;
