How do I find the earliest date in PostgreSQL?

Question

I am working with a Postgres table that has a field named first_contact_date where it is of type date. I am using the the mm/dd/yyyy representation of a date. My query works correctly as written but it orders literally by month.

SELECT first_contact_date 
FROM schema.table 
ORDER BY first_contact_date

For example "10/3/2016" would go after "1/2/2017", even though in a date sense, it should be "1/2/2017" that goes after "10/3/2016".

I have looked at

How to find the earliest and latest date from a database, but its too vague and I am using an order by already.
SQL ordering by earliest date in group, but I am using a different format.
How do I return the record with the earliest date?, ditto with number 2

and

PostgreSQL query where date oldest, but this limits it to one year

How to I structure the query so that the earliest date from a calendar perspective is returned?

cast it to date for ordering order by first_contact_date::date — Vamsi Prabhala
– Vamsi Prabhala, Commented Dec 8, 2017 at 15:26
Typing in: SELECT first_contact_date FROM schema.table order by first_contact_date::date Gives: Error message: invalid input syntax for type date: "" — isakbob
– isakbob, Commented Dec 8, 2017 at 15:28
The Error message: invalid input syntax for type date: "" implies that your column is not of type date. Maybe you should add your table definition plus some data? — joop
– joop, Commented Dec 8, 2017 at 15:48

Edgars T. · Accepted Answer · 2017-12-08 15:45:51Z

2

You can extract interval starting from beginning of year and order by it:

SELECT first_contact_date-date_trunc('year',first_contact_date), first_contact_date
FROM schema.table 
ORDER BY 1;

Edit: Long years might not work properly as duration starting from begining of year might differ after February.

This is a bit dirty, but will work correctly also for long years:

    SELECT to_char(first_contact_date,'MM')::int*100+to_char(first_contact_date,'DD')::int datenum
FROM schema.table 
ORDER BY 1;

edited Dec 8, 2017 at 15:45

answered Dec 8, 2017 at 15:34

Edgars T.

1,1491 gold badge10 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

How do I find the earliest date in PostgreSQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related