How to select from a night period (18hrs to 6hrs)

Asked

Viewed 370 times

4

Description: Count how many times the action occurred in the night period considering the time from 18:00 to 06:00 in the morning.

There is a better way to select this type without having to use or to compare periods?

select count(*) as contador,log_desc,'Noite' from log
where extract(hour from log_data_hora)>=0 and extract(hour fromlog_data_hora)<6  
or  extract(hour from log_data_hora)>18 and extract(hour from log_data_hora)<=23  
group by log_desc,cor_cod

I am using Postgresql.

sql postgresql

2 answers

4

You can use the BETWEEN operator in conjunction with NOT for this:

select count(*) as contador,log_desc,'Noite' from log
where extract(hour from log_data_hora) NOT BETWEEN 6 AND 17
group by log_desc,cor_cod

  • Using this BETWEEN would take the whole period(cunning,late) and I want only the night period, corresponding from 18 hours until 6 am.

  • 1

    @Andrewalex Hence the use of denial (NOT). The expression above reads: 'Return all records where the time is NOT between 6 and 17', which would cover until 17:59.

  • Correct now worked. Simple and functional.

0

My friend, I don’t know which db is using, but in SQL Ansi the query would look like this.

select count(*) as contador,log_desc,'Noite' from log
where log_data_hora >= '2015-03-02 18:00:00.000' and log_data_hora <= '2015-03-03     06:00:00.000'
group by log_desc,cor_cod

  • In case that wouldn’t work, because I don’t want a specific date '2015-03-02', I want to group by independent period of the day, using the reply of @Onosendai worked.

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.