在PostgreSQL中如何在保持自定义日期格式的同时按日期排序 [英] In Postgresql how to order by date while keeping custom date format

问题描述

问题在于，使用to_char会将按日期顺序转换为按ascii顺序.示例:

The issue is that using to_char will turn order by date into order by ascii. Example:

SELECT foo, bar FROM baz ORDER BY foo;

我想使用to_char格式化foo，但是这样做会影响顺序:

I would like to format foo using to_char, but doing this will affect the order:

SELECT to_char(foo,'dd/MM/yyyy') as foo, bar FROM baz ORDER BY foo;

因为foo现在是文本类型.有没有一种方法可以正确地做到这一点?还是仅在代码中?

Because foo now is of type text. There is a way to correctly do this? Or only in the code?

推荐答案

您可以对格式化的列使用其他别名:

You can use a different alias for the formatted column:

SELECT to_char(foo,'dd/MM/yyyy') as formatted_foo, 
       bar 
FROM baz 
ORDER BY foo;

作为替代方案，如果您需要保留foo别名:

As an alternative if you need to keep the foo alias:

select foo,
       bar
from (
  SELECT to_char(foo,'dd/MM/yyyy') as foo,
         foo as foo_date 
         bar 
  FROM baz 
) t
order by foo_date

这篇关于在PostgreSQL中如何在保持自定义日期格式的同时按日期排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！