Reputation: 39
I have the following query which returns the date as dd/mm/yyyy hh:mm:ss
, but I would like to extract the time and date as separate columns, how can I do this please? I am new to PostgreSQL
Thanks 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
Upvotes: 0
Views: 3249
Reputation: 349
Use to_char
. See 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)
Upvotes: 0
Reputation: 51659
use explicit casting, for time:
es.created::time
and for date:
es.created::date
Eg:
t=# select now()::time(0) "Time",now()::date "Date";
Time | Date
----------+------------
08:19:59 | 2017-06-06
(1 row)
Upvotes: 1