Handling time using PostgreSQL

Question

I have a table with a date and time field. I'm having difficulty understanding how I deal with this, partially as I don't understand how time can be converted to a number. I made a table using the following command:

CREATE TABLE tracking.time_record
(
  date date, 
  "time" time without time zone,
  id character(100)
)

An example of my data is as follows:

"2012-04-18" | "18:33:19.612" | "2342342384" 

How can I run a query such that I can examine all of the id values that have a time value > 10 pm on a certain day, for example?

I realize that as my time is stored in a character type variable so something like this does not work:

SELECT * FROM tracking.time_record 
WHERE "time" > "04:33:38.884" AND date > "2012-04-18"

(This is my first exploration of time/date tracking - I should probably have chosen different column names)

Answer 1

Neither of the answers so far captures your actual problem(s).

While an explicit cast to the appropriate type certainly doesn't hurt, it is not necessary. PostgreSQL coerces a string literal to the appropriate type automatically.

Your problems stem from basic syntax errors:
Double quotes are for identifiers: "MyColumn" - and only necessary for otherwise illegal identifiers (mixed case, reserved word, ..) which should be avoided to begin with.
Single quotes are for values: 'string literal'.

You might be interested in the well written chapters on identifiers and constants of the PostgreSQL manual.

While we are at it, never use date or time as column names. Both are reserved words in every SQL standard and type names in PostgreSQL. This will lead to confusing code and error messages.

I would recommend to just use a single timestamp column instead of separate date and time: And you almost certainly don't want character(100) as data type, ever - especially not for an id column. This blank-padded type is basically only there for historic reasons. Consider text or varchar instead:

• Any downsides of using data type "text" for storing strings?

Could look like this:

CREATE TABLE tbl (
   tbl_id text CHECK(length(id) <= 100)
 , ts timestamp
);

Cast to time or date where you only need these components, it's short and cheap:

SELECT ts::time AS the_time, ts::date AS the_date FROM tbl;

Use date_trunc() or extract() for more specific needs.
To query for ... id values that have a time value > 10 pm on a certain day:

SELECT *
FROM   tbl
WHERE  ts::time > '22:00'
AND    ts::date = '2012-04-18';

Or, for any continuous time period:

...
WHERE  ts > '2012-04-18 22:00'::timestamp
AND    ts < '2012-04-19 00:00'::timestamp;

The second form can use a plain index on ts better and will be faster in such cases for big tables.

More about timestamp handling in PostgreSQL:

• Ignoring timezones altogether in Rails and PostgreSQL