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

An explicit cast to the appropriate type doesn't hurt. But it's not necessary here. PostgreSQL coerces string literals 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'. See:

• Are PostgreSQL column names case-sensitive?

• The manual on identifiers and constants.

While being at it, don't use date or time as column names. Both are reserved words in every SQL standard and type names in PostgreSQL. Would lead to confusing code and error messages.

Consider a single timestamp column instead of separate date and time.

And you 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 time zones for Postgres timestamps

Answer 2

You can use the date_part function to get the hour

select id 
from tracking.time_record
where date_part('hour', time) >= 22
  and date = '2012-04-18'

The >= 22 instead of > 22 it's to catch intervals between 10pm and 11pm. This also catches 10:00:00pm. but its't hard to adapt the query to catch the right intervals

You have a working example here.

Answer 3

comparing time and date values should work fine in postgresql, just make sure you convert your strings to the appropriate types:

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