CODEWITHSUNDEEP
sqlpostgresqldatetime
Python241820
Python241820

Reputation: 1557

problems with sql hours ( postgresql )

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.

Upvotes: 0

Views: 76

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 2

Related Questions