Wildcard for date in SELECT from type TIMESTAMP

#1

Dear PostgreSQL users,
How can I select all entries having a time of earlier than for example
11:00:00 from a column with data type TIMESTAMP, no matter what the date is?
There has probably already been someone with this problem, but I really
wasn't able to find anything here or in the web.
Thanks in advance,
Julia

#2

Julia Jacobson wrote on 21.08.2010 19:32:

Quote:

Dear PostgreSQL users,
How can I select all entries having a time of earlier than for example
11:00:00 from a column with data type TIMESTAMP, no matter what the date
is?
There has probably already been someone with this problem, but I really
wasn't able to find anything here or in the web.
Thanks in advance,
Julia

this should work:

SELECT *
FROM the_table_with_no_name
WHERE cast(the_timestamp_column as time) <= TIME '11:00:00'

Thomas

#3

Yes, it does. Thanks a lot.

Quote:

Julia Jacobson wrote on 21.08.2010 19:32:
Dear PostgreSQL users,
How can I select all entries having a time of earlier than for example
11:00:00 from a column with data type TIMESTAMP, no matter what the date
is?
There has probably already been someone with this problem, but I really
wasn't able to find anything here or in the web.
Thanks in advance,
Julia

this should work:

SELECT *
FROM the_table_with_no_name
WHERE cast(the_timestamp_column as time) <= TIME '11:00:00'

Thomas

#4

Julia Jacobson <julia.jacobson (AT) arcor (DOT) de> wrote:

Quote:

Yes, it does. Thanks a lot.

this should work:

SELECT *
FROM the_table_with_no_name
WHERE cast(the_timestamp_column as time) <= TIME '11:00:00'

Just for info:

If you have a large table and you want to improve the speed, you can
create an functional index:

create index a_index on a_table((a_column::time));

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Wildcard for date in SELECT from type TIMESTAMP

comp.databases.postgresql comp.databases.postgresql

Wildcard for date in SELECT from type TIMESTAMP - 08-21-2010 , 12:32 PM

Re: Wildcard for date in SELECT from type TIMESTAMP - 08-21-2010 , 01:07 PM

Re: Wildcard for date in SELECT from type TIMESTAMP - 08-21-2010 , 02:20 PM

Re: Wildcard for date in SELECT from type TIMESTAMP - 08-23-2010 , 01:02 AM