CODEWITHSUNDEEP
postgresqldate
KathH
KathH

Reputation: 39

PostgreSQL get date

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

Answers (2)

kimdasuncion12
kimdasuncion12

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

Vao Tsun
Vao Tsun

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

Related Questions