PostgreSQL get date

Question

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

Vao Tsun · Accepted Answer · 2017-06-06 08:18:44Z

1

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)

answered Jun 6, 2017 at 8:18

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

KathH Over a year ago

This has worked to a point, the date and time are now separate (This is great), however the format of the date is still dd/mm/yyyy hh:mm:ss eg 06/06/2017 00:00:00

Vao Tsun Over a year ago

this is totally different question - ask it. but please first look for the existing answer - I'm sure it is answered on SO already

pozs Over a year ago

@KathH that's just how your client displays it. PostgreSQL sends a date only for es.created::date (which does not have any time component in it).

kimdasuncion12 · Accepted Answer · 2017-06-06 08:46:03Z

0

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)

answered Jun 6, 2017 at 8:46

kimdasuncion12

3492 gold badges4 silver badges17 bronze badges

Collectives™ on Stack Overflow

PostgreSQL get date

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related