PostgreSQL获取日期

Question

我有下面的查询，它返回dd/mm/yyyy hh:mm:ss格式的日期，但是我想提取时间和日期作为单独的列，我该怎么做呢？我是PostgreSQL新手

谢谢Kath

select 
    up.mis_id, 
    up.name, 
    up.surname, 
    es.created as "signed in", 
    es1.created as "signed out", 
    bb.name as "location"


from users_person as up

join users_role as ur on ur.id = up.role_id
join events_event as ee on ee.user_id = up.id
join events_swipe as es on es.id = ee.sign_in_id
join events_swipe as es1 on es1.id = ee.sign_out_id
join buildings_building as bb on bb.id = es.location_id

Answer 1

对于时间，使用显式强制转换：

es.created::time

对于日期：

es.created::date

例如：

t=# select now()::time(0) "Time",now()::date "Date";
   Time   |    Date
----------+------------
 08:19:59 | 2017-06-06
(1 row)

Answer 2

使用to_char。请参阅Postgres documentation

select to_char(current_timestamp, 'HH12:MI:SS') as time,  
       to_char(current_timestamp, 'mm/dd/yyyy') as date;
       time   |    date    
    ----------+------------
     04:43:13 | 06/06/2017
    (1 row)