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)

Solution 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

Solution 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.

Solution 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'

Comments

[Andrew]

Note that this will find records from 2012-04-18 onwards after 4:33am. (so nothing before that day and also nothing from before 4:33am on any day). What you probably want is a timestamp column.

[Erwin Brandstetter]

Casting (while no harm) is not necessary in this situation. Postgres coerces a string literal to the matching type automatically here. Syntax errors are the problem.

[Erwin Brandstetter]

date_part() is not necessary, either. "time" > '22:00' would do.

[user1346466]

You are right. I would still recommend the cast. Is it comparing strings or dates or something else? You really can't tell without looking at the table specification.

[Erwin Brandstetter]

BTW, I doubt you actually want character(100) for your id. This blank-padded type is basically only there for historic reasons. Consider text or varchar instead.