Postgresql, comparing text as datetime

Question

I have a table with two columns of "text" type where in one is a date in format 10.03.2013. and in second is time in format 15:45:32. Since both are text I would like to know if is possible with PostgreSQL those strings compare with windows DateTime value.

I was try to get records with BETWEEN() but didn't get nothing.

"(mydate BETWEEN " & fromdate.Date.ToString & _
       " AND " & todate.Date.ToString & ") AND _
 (mytime BETWEEN " & fromdate.TimeOfDay.ToString & _
       " AND " & todate.TimeOfDay.ToString & ")"

mydate and mytime are columns while fromdate and to date are windows datetime types.

Any advice?

What language is this supposed to be? Why not first construct a timestamp type of value from the separate date+time fields? — wildplasser
– wildplasser, Commented Jan 1, 2013 at 22:09
This is visual basic where i make commands like strings. Second, because data already exists. — Wine Too
– Wine Too, Commented Jan 1, 2013 at 22:12
Or maybe I understand wrong? Can I construct timmestamp from showed data and mented datatype (text)? And how? — Wine Too
– Wine Too, Commented Jan 1, 2013 at 22:15
Do not store dates or timestamp as text (or varchar). That will give you a lot of trouble in the long run. Always use the proper datatype. — user330315
– user330315, Commented Jan 1, 2013 at 23:09

Clodoaldo Neto · Accepted Answer · 2013-01-02 10:02:49Z

4

Turn those columns into a timestamp type:

select ...
from ...
where
    to_timestamp(mydate || mytime, 'DD.MM.YYYYHH24:MI:SS') between
        '2012-01-20 10:23:12'
        and
        '2012-01-20 11:47:03'

edited Jan 2, 2013 at 10:02

answered Jan 1, 2013 at 22:43

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

5 Comments

Wine Too Over a year ago

Can't get that working. I wrongly represent date in question. Those date is in format 10.03.2013 without last point. May this be a problem?

Clodoaldo Neto Over a year ago

@user973238 Yes that can be a problem. I edited the answer to accommodate the date format.

Clodoaldo Neto Over a year ago

@user973238 I mean not the point absence but the dd.mm.yyyy format. I first guessed it was mm.dd.yyyy as in the USA. But after you comment I saw you are from Croatia so it probably is dd.mm.yyyy like here in Brazil.

Wine Too Over a year ago

Of course Colorado, thank you, I will fight forward to get results in this situation.

Wine Too Over a year ago

I finally GET IT :) PostgreSQL is amazing!

Collectives™ on Stack Overflow

Postgresql, comparing text as datetime

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related