# Spliting Datetime into 2 Columns for Restaurant Reservation Schema?

Using PostgreSQL.

The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a single column I'm constantly going to have to break this apart and search via date_trunc.

While PostgreSQL allows one to create an index based on a function, explain shows that this is not being used for conditions using date_trunc.

Table:

development2=# \d reservations
Table "public.reservations"
Column      |            Type             |                         Modifiers
-----------------+-----------------------------+-----------------------------------------------------------
id              | integer                     | not null default nextval('reservations_id_seq'::regclass)
tops            | integer                     | not null
price           | numeric                     |
state           | integer                     | not null
restaurant_id   | integer                     | not null
seller_id       | integer                     |
customer_id     | integer                     |
created_at      | timestamp without time zone | not null
start           | timestamp without time zone |
Indexes:
"reservations_pkey" PRIMARY KEY, btree (id)
"start_date_index" btree (date_trunc('day'::text, start))
Foreign-key constraints:
"reservations_customer_id_fk" FOREIGN KEY (customer_id) REFERENCES users(id)
"reservations_restaurant_id_fk" FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
"reservations_seller_id_fk" FOREIGN KEY (seller_id) REFERENCES users(id)


Query:

development2=# explain select id,start from reservations where date_trunc('day', start) = '2014-03-14';
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on reservations  (cost=0.00..1.01 rows=1 width=12)
Filter: (date_trunc('day'::text, start) = '2014-03-14 00:00:00'::timestamp without time zone)
(2 rows


)

Not even sure how to approach the time search, to_char + IMMUTABLE I guess...

-
Show the SQL, the explain output, the table and index definitions, and your PostgreSQL version. Edit your question, comment here when done. –  Craig Ringer Mar 14 '14 at 3:04
Try to add some more data/dates and you will see your index being used. For a one row table it is much easier to read the whole table. –  dezso Mar 14 '14 at 6:09

### timestamp vs. date & time

If you are interested in the date part of your timestamp, it is cheaper to just cast it. This results is an actual date, while date_trunc() returns a timestamp! (You could cast that to date, but don't bother.) Your index should be:

CREATE INDEX start_date_index ON reservations (cast(start AS date));


Use the more verbose standard notation cast(col AS type) in the index definition. You can still use the Postgres shorthand start::date in queries to match that:

SELECT id, start
FROM   reservations
WHERE  start::date = '2014-03-14'::date;


You can to similar things with start::time if needed.
Either way, I would certainly not split the date and time part. A timestamp is almost always the superior design.

### Index tuning

Depending on your actual use cases, you could make that a multi-column index including the id:

CREATE INDEX start_id_date_index ON reservations (cast(start AS date),id);


That would serve the above query perfectly, possibly even with an index-only scan. And since you are now using an actual date in the index now (4 bytes), an additional integer column (also 4 bytes) is just perfect for performance. Details in this related answer:
Is a composite index also good for queries on the first field?

What's more, you can also just use a plain index on the timestamp:

CREATE INDEX start_start_index ON reservations (start);


Just make sure your WHERE conditions are sargable. Like:

SELECT id, start
FROM   reservations
WHERE  start >= '2014-03-14 0:0'::timestamp
WHERE  start <  '2014-03-15 0:0'::timestamp;


Will use the index.

The best solution depends on the sum of all your requirements.

Also, Postgres should still be using your original, less optimized index. How many rows are there in your table? With only a few, a sequential scan is faster and you won't seen an index scan.

Aside: don't use start as identifier. It's a reserved word in standard SQL.

-
Hey thanks for the info. My table only had a few rows so as you and @dezso pointed out a seq scan is performed. One question: is it possible to search on time, ignoring seconds? –  Dumb E. Mar 15 '14 at 3:39
@DumbE.: search on time: sure. I suggest you open a new question with your exact requirements so you get the best solution. You can always link to this one for context. –  Erwin Brandstetter Mar 15 '14 at 10:30