PostgreSQL, getting min and max date from text column

Question

I have such situation in table:

1   01.02.2011
2   05.01.2011
3   06.03.2012
4   07.08.2011
5   04.03.2013
6   06.08.2011
7   
8   02.02.2013
9   04.06.2010
10  10.10.2012
11  04.04.2012

where first column is id (INT) and second column is TEXT in which may be written date in format 'dd.mm.yyyy'.

I would like to get:
1) lowest entered date in whole table and highest entered date in whole table.
2) lowest entered date in year 2012 and highest entered date in year 2012.

Lowest and highest date in year may be a same (like for year 2010) or field may be empty (like in row 7).

I am tying to use TO_TIMESTAMP but unsuccessfully. Example:

SELECT (TO_TIMESTAMP(mydatetxt, 'DD.MM.YYYY'))   
FROM " & myTable & "   
ORDER BY (TO_TIMESTAMP(mydatetxt, 'DD.MM.YYYY')) ASC LIMIT 1

Also with BETWEEN I don't get wanted result.

How to write those two queries?

SOLUTION:
Thanks for all suggestions.
Igor's solution is most suitable and simple enough for me.

Dim sqlText As String = "SELECT min(to_date(nullif(mydate,''), 'DD.MM.YYYY')) FROM " & mytable
cmd1 = New NpgsqlCommand(sqlText, conn)
min = CDate(cmd1.ExecuteScalar())
If Not IsDate(min) Then
    min = CType(CDate("01.01." & myyear) & " 00:00:00", Date)
End If
fromdate = CType(CDate(min), Date)

sqlText = "SELECT max(to_date(mydate, 'DD.MM.YYYY')) FROM " & mytable
cmd1 = New NpgsqlCommand(sqlText, conn)
max = CDate(cmd1.ExecuteScalar())
If Not IsDate(max) Then
    max = CType(CDate("31.12." & myyear) & " 23:59:59.9999", Date)
End If
todate = CType(CDate(max), Date)

I am update question with example query for getting minimal date on your request. I don't know why my queries don't work. If I would know then help would not be needed. — Wine Too
– Wine Too, Commented Aug 12, 2013 at 9:43
How does the query don't work? Any error messages? Wrong results? Something else? — Ihor Romanchenko
– Ihor Romanchenko, Commented Aug 12, 2013 at 9:46
I am using this through NET and npgsql adapter, so on executescalar I get error message: 'Specified cast is not valid'. Maybe becuse I have only dat but not time for converting to timestamp? — Wine Too
– Wine Too, Commented Aug 12, 2013 at 9:51

Ihor Romanchenko · Accepted Answer · 2013-08-12 09:57:41Z

3

Try something like:

SELECT max(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY')),
       min(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY'))
FROM table_name;

SELECT max(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY')),
       min(to_date(nullif(mydatetxt,''), 'DD.MM.YYYY'))
FROM table_name
WHERE date_part('year',to_date(nullif(mydatetxt,''), 'DD.MM.YYYY')) = 2012;

edited Aug 12, 2013 at 9:57

answered Aug 12, 2013 at 9:50

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

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

1 Comment

Wine Too Over a year ago

That one is most simple and highly usable for my case. Thanks a lot.

Collectives™ on Stack Overflow

PostgreSQL, getting min and max date from text column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related