DATE in BETWEEN

[Former Member]

Senhores,

Gostaria de saber como faço para utilizar timestamp nas cláusulas BETWEEN do WHERE.

(+Gentlemen,

I wonder how do I use the timestamp BETWEEN of the WHERE clauses.+)

Example:

( table.row_date BETWEEN (DATE(SUBDATE(timestamp,7)) || ' ' || TIME('00:00:00')) AND (DATE(timestamp|| ' ' || TIME('23:59:59')) )

Está correto?

(Correct?)

[lbreddemann · Accepted solution]

Hi Marcel,

the concatenation you're doing there is unnecessary.

Instead this should work (assuming that rowdate_ is a TIMESTAMP datatyp as I've seen it in your database...)

table.row_date BETWEEN timestamp(chr(date_column), '00:00:00') 
               AND timestamp(chr(date_column), '23:59:59')

regards,

Lars

[Former Member · Accepted solution]

Olá Lars,

Me parece que essa query trará as informações de HOJE.. e seu eu precisar trazer dias anteriores ou posteriores, utilizo o SUBDATE e ADDDATE ?

(I think that will query the information from TODAY .. and I have to bring your day earlier or later, use the SUBDATE and ADDDATE?)

Realizando um teste, apresentou o seguinte erro:

(Conducting a test, made the following error:)

Datetime field overflow;-3049 POS(1220) Invalid time format:ISO.

Verifiquei o formato do campo e está como TIMESTAMP.

(I checked the format of the field and is as TIMESTAMP.)

Edited by: Marcel Cerqueira on Nov 3, 2008 12:48 PM

[lbreddemann · Accepted solution]

Hi Marcel,

here we go again...

> (<i>I think that will query the information from TODAY .. and I have to bring your day earlier or later, use the SUBDATE and ADDDATE?</i>)

Nope - it does what I wrote it would do. Deliver the rows where the date is in-between your comparison date.

Here's another simple example:

sqlcli -d TEST -u dba,dba

Welcome to the MaxDB interactive terminal.

Type:  \h for help with commands
       \q to quit

sqlcli TEST=>
sqlcli TEST=> drop table datetable
0 rows affected (44.978 msec)

sqlcli TEST=> create table datetable (id integer  default serial(1), arrival timestamp,
                   departure timestamp, checkout timestamp, primary key (id))
0 rows affected (2027 usec)

sqlcli TEST=> insert into datetable (arrival) values (timestamp('1976-08-12', '00:00:00'))
1 row affected (3167 usec)

sqlcli TEST=> insert into datetable (arrival) values (timestamp('1976-09-05', '00:00:00'))
1 row affected (1817 usec)

sqlcli TEST=> insert into datetable (arrival) values (timestamp('1976-10-03', '00:00:00'))
1 row affected (1653 usec)

sqlcli TEST=> insert into datetable (arrival) values (timestamp('1978-05-12', '00:00:00'))
1 row affected (2118 usec)

sqlcli TEST=> insert into datetable (arrival) values (timestamp('1983-06-02', '00:00:00'))
1 row affected (1487 usec)

sqlcli TEST=> update datetable set departure = adddate(arrival, 10)
5 rows affected (1081 usec)

sqlcli TEST=> update datetable set checkout = adddate (arrival, mod(id,3) * 8)
5 rows affected (1273 usec)

sqlcli TEST=> select * from datetable
| ID             | ARRIVAL                    | DEPARTURE                  | CHECKOUT                   |
| -------------- | -------------------------- | -------------------------- | -------------------------- |
|              1 | 1976-08-12 00:00:00.000000 | 1976-08-22 00:00:00.000000 | 1976-08-20 00:00:00.000000 |
|              2 | 1976-09-05 00:00:00.000000 | 1976-09-15 00:00:00.000000 | 1976-09-21 00:00:00.000000 |
|              3 | 1976-10-03 00:00:00.000000 | 1976-10-13 00:00:00.000000 | 1976-10-03 00:00:00.000000 |
|              4 | 1978-05-12 00:00:00.000000 | 1978-05-22 00:00:00.000000 | 1978-05-20 00:00:00.000000 |
|              5 | 1983-06-02 00:00:00.000000 | 1983-06-12 00:00:00.000000 | 1983-06-18 00:00:00.000000 |

5 rows selected (11.593 msec)

Let's stop a moment here.

What do we have here?

In our table, we've five entries with different arrival and departure timestamps.

As I arranged it the departures are always ten days after the arrivals.

Now there is the CHECKOUT column that contains timestamps as well.

But only three of these CHECKOUT values fall between the ARRIVAL and the DEPARTURE timestamp.

Here's how to figure out which ones these are:

sqlcli TEST=> select * from datetable where date(checkout) between date(arrival) and date(departure)
| ID             | ARRIVAL                    | DEPARTURE                  | CHECKOUT                   |
| -------------- | -------------------------- | -------------------------- | -------------------------- |
|              1 | 1976-08-12 00:00:00.000000 | 1976-08-22 00:00:00.000000 | 1976-08-20 00:00:00.000000 |
|              3 | 1976-10-03 00:00:00.000000 | 1976-10-13 00:00:00.000000 | 1976-10-03 00:00:00.000000 |
|              4 | 1978-05-12 00:00:00.000000 | 1978-05-22 00:00:00.000000 | 1978-05-20 00:00:00.000000 |

3 rows selected (693 usec)

The DATE( ) conversion function is used here, since I just wanted to focus on the date component of the timestamp.

Of course I could have also just left it.

So if you don't get what you wanted here, although your SQL is right - check your data.

Concerning your

>

Datetime field overflow;-3049 POS(1220) Invalid time format:ISO.

What data did you tried to enter into which field/function?

It looks like you're trying to put the data into a wrong format.

Check [Date and Time Format (datetimeformat)|http://maxdb.sap.com/doc/7_6/48/0d8018b4f211d2a97100a0c9449261/content.htm] about time and date formats.

regards,

Lars

p.s.

In general, if you know that you're going to need just the DATE part of a timestamp - don't use timestamp but DATE as the datatype...

[Former Member · Accepted solution]

Olá Lars

,

A nossa comunicação é prejudicada devido à barreira do idioma, mas espero que consigamos superá-la.

O meu problema é:

Na minha tabela existe uma coluna chamada rcl_dthr (TIMESTAMP) na table 'rcl'. Essa coluna guarda a informação da hora em que aconteceu o atendimento e preciso buscar em uma das querys, os atendimentos do dia e em outra, o que foi atendido nos últimos 7 dias (sete dias até hoje).

(+Our communication is impaired because of the language barrier, but I hope we can overcome it.

My problem is:

In my table there is a column called rcl_dthr (TIMESTAMP) on the table 'rcl'. This column stores the information of what happened in the hours and must seek care in one of querys, the attendances of the day and in another, which has been in the last 7 days (seven days until today).+)

Pelo que entendi da sua query (1), ela faz uma relação entre colunas da mesma table, eu preciso utilizar a hora do sistema.

(As I understand it from your query (1), it is a relationship between columns of the table, I need to use the system time.)

1. select * from datetable where date(checkout) between date(arrival) and date(departure)

Para resolver meu problema, ficaria assim?

*Today*: select * from rcl where date(rcl_dthr) = date(timestamp)

AND

*Seven days ago until today*: select * from rcl where date(rcl_dthr) between date(subdate(timestamp, 7)) and date(timestamp)

Thanks again.

Regards,

Marcel

[lbreddemann · Accepted solution]

> (<i>Our communication is impaired because of the language barrier, but I hope we can overcome it.

We'll see how this turns out...

> My problem is:

> In my table there is a column called rcl_dthr (TIMESTAMP) on the table 'rcl'. This column stores the information of what happened in the hours and must seek care in one of querys, the attendances of the day and in another, which has been in the last 7 days (seven days until today).</i>)

> (<i>As I understand it from your query (1), it is a relationship between columns of the table, I need to use the system time.</i>)

Yes, I took another column from the same table as that is what I understood from your example.

Doing it with the current time at the database server, is not more difficult at all:

First let's enter some data into the test table that would actually return some rows for a query with the current date:

insert into datetable (arrival, departure) values ('2008-11-03 13:15:23', '2008-11-08 12:00:00')
//	
insert into datetable (arrival, departure) values ('2008-10-28 14:05:00', '2008-11-01 12:00:00')
//
insert into datetable (arrival, departure) values ('2008-10-27 23:15:23', '2008-10-31 12:00:00')

Ok, now we use the date function to deliver the current date.

NOTICE: when used with arguments like date(*'2008-10-27 23:15:23'') then date is a conversion function.

When used without arguments it just delivers the current date.

Therefore instead of:

>

1. select * from datetable where date(checkout) between date(arrival) and date(departure)

we would say:

 select * from datetable where date between date(arrival) and date(departure)
| ID             | ARRIVAL                    | DEPARTURE                  | CHECKOUT                   |
| -------------- | -------------------------- | -------------------------- | -------------------------- |
|              8 | 2008-11-03 13:15:23.000000 | 2008-11-08 12:00:00.000000 | ?                          |

1 row selected (75.808 msec)

Now, what if you want to know the rows, where say arrival was within the last week?

select * from datetable where date(arrival)  between subdate(date, 7) and date
| ID             | ARRIVAL                    | DEPARTURE                  | CHECKOUT                   |
| -------------- | -------------------------- | -------------------------- | -------------------------- |
|              8 | 2008-11-03 13:15:23.000000 | 2008-11-08 12:00:00.000000 | ?                          |
|              9 | 2008-10-28 14:05:00.000000 | 2008-11-01 12:00:00.000000 | ?                          |

2 rows selected (914 usec)

So, basically you've already been there, except the misuse of "date(timestamp)".

regards,

Lars

[Former Member · Accepted solution]

Olá Lars,

Ao utilizar da forma como indicou:

(By using the way indicated)

 ... ( date(rcl.rcl_dthr) BETWEEN subdate(date,1) AND date ) ... 

está dando o seguinte erro:

(is giving the following error)

 General error;-9211 POS(1) System error: KB Stack type illegal. 

mas quando tiro a condição,e coloco a data manualmente, funciona.

(but when the shooting condition, and put the date manually, it works)

Fiz outro teste, mais simples e funcionou.

(I did another test, simpler and more work.)

SELECT rcl_dthr FROM rcl WHERE ( date(rcl.rcl_dthr) BETWEEN subdate(date,2) AND date ) LIMIT 100

Agora fico só precisando de uma ajuda com a mensagem de erro para fechar este tópico.

(Now I just need a little help with the error message to close this topic.)

Grateful for the help,

Marcel

[lbreddemann · Accepted solution]

>

 General error;-9211 POS(1) System error: KB Stack type illegal. 

This is due to the extremely old MaxDB patch you're using.

To import your backup I used a 7.6 Build 34 and did not get this error.

> (<i>I did another test, simpler and more work.</i>)

>

SELECT rcl_dthr FROM rcl WHERE ( date(rcl.rcl_dthr) BETWEEN subdate(date,2) AND date ) LIMIT 100

Looks Ok, but be carefull which the TOP n clause. Depending on what you want to archieve the clause will not work as expected. E.g. it stops at row number 100 that fits. If you do ORDER or GROUP by the TOP clause will be applied before those steps.

> (<i>Now I just need a little help with the error message to close this topic.</i>)

As I wrote above: use a version that was not outdated years ago.

regards,

Lars

[Former Member · Accepted solution]

Olá Lars,

Muito obrigado pelo empenho. Conversarei como o administrador do banco de dados para ver o que conseguimos evoluir das questões que você levantou.

Mais uma vez, muito obrigado!

Atenciosamente,

(+Thank you for commitment. Speak as a database administrator to see what we evolve the issues you raised.

Again, thank you!

Regards+)

Marcel