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.5k13 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

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)

Collectives™ on Stack Overflow

PostgreSQL get date

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related