problems with sql hours ( postgresql )

Question

show data from travel where time is less than 18:30:

insert into schedules(code, datetime, street)
values('004',TO_DATE('02/01/15 17:00','DD/MM/YY HH24:MI'),'street1');

insert into schedules(code, datetime, street)
values('004',TO_DATE('02/01/15 17:30','DD/MM/YY HH24:MI'),'street2');

insert into schedules(code, datetime, street)
values('004',TO_DATE('02/01/15 18:00','DD/MM/YY HH24:MI'),'street3')

insert into schedules(code, datetime, street)
values('005',TO_DATE('01/01/15 18:00','DD/MM/YY HH24:MI'),'street4');

insert into schedules(code, datetime, street)
values('005',TO_DATE('01/01/15 18:30','DD/MM/YY HH24:MI'),'street5');

insert into schedules(code, datetime, street)
values('005',TO_DATE('01/01/15 20:00','DD/MM/YY HH24:MI'),'street6')

This query:

select * 
from schedules where TO_CHAR(datetime,'HH24:MI')<'18:30');

shows all rows.

select * from schedules where TO_CHAR(datetime,'HH24:MI')<'18:30'); — Python241820
– Python241820, Commented Mar 11, 2016 at 10:00
Don't post code in comments. Edit your question (I have done that for you for now, but please remember the next time) — user330315
– user330315, Commented Mar 11, 2016 at 10:26

user330315 · Accepted Answer · 2016-03-11 10:18:46Z

2

Your first problem is, that you are storing the data incorrectly.

This:

insert into schedules(code, datetime, street)
values ('005',TO_DATE('01/01/15 20:00','DD/MM/YY HH24:MI'),'street6')

inserts a DATE value, without a time into the table (because to_date() returns a date, not a timestamp)

If you want to store a real timestamp value (date & time), you need to use to_timestamp() or an ANSI timestamp literal:

insert into schedules(code, datetime, street)
values ('005',to_timestamp('01/01/15 20:00','DD/MM/YY HH24:MI'),'street6');

I personally prefer to use ANSI timestamp literals:

insert into schedules(code, datetime, street)
values ('005',timestamp '2015-01-01 20:00:00','street6');

To retrieve rows base on only the time, you should compare time values, not strings.

Assuming datetime is defined as a timestamp column (and **not* as a date as you can get all schedules before 18:30 using this:

select *
from schedules
where datetime::time < time '18:30'

The expression datetime::time will extract only the time part of the timestamp as a "real" time value, not as a string, which can be compared to a proper time literal

answered Mar 11, 2016 at 10:18

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Python241820 Over a year ago

Thank you very much. I am so grateful for you hurry response.

Collectives™ on Stack Overflow

problems with sql hours ( postgresql )

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related