postgresql 将字符变化字段转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4758387/
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
Casting character varying field into a date
提问by Pasted
I have two tables,
我有两张桌子，
details
id integer primary key
onsetdate Date
questionnaires
id integer primary key
patient_id integer foreign key
questdate Character Varying
Is it possible to make a SELECT statement that performs a JOIN on these two tables, ordering by the earliest date taken from a comparision of onsetdate and questdate (is it possible for example to cast the questdate into a Date field to do this?)
是否可以在这两个表上执行 JOIN 的 SELECT 语句，按从 onsetdate 和 questdate 的比较中获取的最早日期排序（例如，是否可以将 questdate 转换为 Date 字段来执行此操作？）
Typical format for questdate is "2009-04-22"
任务日期的典型格式是“2009-04-22”
The actual tables have an encyrpted BYTEA field for the onsetdate - but I'll leave that part until later (the application is written in RoR using 'ezcrypto' to encrypt the BYTEA field).
实际的表有一个加密的 BYTEA 字段用于 onsetdate - 但我会把那部分留到以后（应用程序是在 RoR 中编写的，使用“ezcrypto”来加密 BYTEA 字段）。
回答by Marco Mariani
something like
就像是
SELECT...
FROM details d
JOIN quesionnaires q ON d.id=q.id
ORDER BY LEAST (decrypt_me(onsetdate), questdate::DATE)
maybe? i'm not sure about the meaning of 'id', if you want to join by it or something else
也许？我不确定“id”的含义，如果你想加入它或其他什么
By the way, you can leave out the explicit cast, it's in ISO format after all. I guess you know what to use in place of decrypt_me()
顺便说一句，您可以省略显式转换，毕竟它是 ISO 格式。我猜你知道用什么来代替decrypt_me()
回答by Maxim Sloyko
There is a date parsing function in postgres: http://www.postgresql.org/docs/9.0/interactive/functions-formatting.html
postgres 中有一个日期解析功能：http: //www.postgresql.org/docs/9.0/interactive/functions-formatting.html
Look for the to_timestamp function.
查找 to_timestamp 函数。
回答by Mike Sherrill 'Cat Recall'
PostgreSQL supports the standard SQL CAST() function. (And a couple others, too.) So you can use
PostgreSQL 支持标准的 SQL CAST() 函数。（还有其他几个。）所以你可以使用
CAST (questdate AS DATE)
as long as all the values in the column 'questdate' evaluate to a valid date. If this database has been in production for a while, though, that's pretty unlikely. Not impossible, but pretty unlikely.
只要“questdate”列中的所有值都计算为有效日期。但是，如果这个数据库已经投入生产一段时间，那是不太可能的。并非不可能，但不太可能。
