Timestamp calculation

António Barata

Timestamp calculation
Hi
I have a process that is supposed to run somewhere after 00:00, and I want to download and delete all the rows from a table, from the day before
I was thinking of doing it this way:
UNLOAD TABLESPACE
LOCK NO, QUIESCE NO
SELECT * FROM OLD TABLE
 (DELETE FROM PARTS
    WHERE " condition for data from the day before " )
but couldn't figure a easy way to deal with the TIMESTAMP.
I would appreciate some help please
Thanks

 

 

Larry Jardine

Timestamp calculation
(in response to António Barata)
There are several ways to do this, including using the DATE function to isolate the date portion of a timestamp or using the TRUNC_TIMESTAMP function.

Larry Jardine
Database Advisor, Aetna, a CVS Health Company

[CVS]
CONFIDENTIALITY NOTICE: This communication and any attachments may contain confidential and/or privileged information for the use of the designated recipients named above. If you are not the intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify the sender immediately by email or telephone and destroy all copies of this communication and any attachments.

From: António Barata <[login to unmask email]>
Sent: Monday, July 27, 2020 6:42 AM
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - Timestamp calculation

**** External Email - Use Caution ****
Hi
I have a process that is supposed to run somewhere after 00:00, and I want to download and delete all the rows from a table, from the day before
I was thinking of doing it this way:
UNLOAD TABLESPACE
LOCK NO, QUIESCE NO
SELECT * FROM OLD TABLE
(DELETE FROM PARTS
WHERE " condition for data from the day before " )
but couldn't figure a easy way to deal with the TIMESTAMP.
I would appreciate some help please
Thanks





-----End Original Message-----

NOTICE TO RECIPIENT OF INFORMATION:
This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately.

This e-mail may also contain protected health information (PHI) with information about sensitive medical conditions, including, but not limited to, treatment for substance use disorders, behavioral health, HIV/AIDS, or pregnancy. This type of information may be protected by various federal and/or state laws which prohibit any further disclosure without the express written consent of the person to whom it pertains or as otherwise permitted by law. Any unauthorized further disclosure may be considered a violation of federal and/or state law. A general authorization for the release of medical or other information may NOT be sufficient consent for release of this type of information.

Thank you. Aetna
Attachments

  • image001.png (12.9k)

Shay Miller

RE: Timestamp calculation
(in response to António Barata)

Hello.

 

Use this:

DELETE 

FROM table

WHERE col1 BETWEEN TIMESTAMP(CURRENT DATE - 1 DAY,6) AND

                                        TIMESTAMP(CURRENT DATE ,6) - 1 MICROSECOND

 

This will always delete all of yesterday's data no matter in which hour you run.

 

Cheers,

Shay. 

Ant&#243;nio Barata

Timestamp calculation
(in response to Shay Miller)
HelloThanks Shay, it worked perfectly.

António Barata 

On Monday, 27 July 2020, 13:38:58 WEST, Shay Miller <[login to unmask email]> wrote:


Hello.

 

Use this:

DELETE 

FROM table

WHERE col1 BETWEEN TIMESTAMP(CURRENT DATE - 1 DAY,6) AND

                                        TIMESTAMP(CURRENT DATE ,6) - 1 MICROSECOND

 

This will always delete all of yesterday's data no matter in which hour you run.

 

Cheers,

Shay. 

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Try BCV5, the BCV5 Masking Tool, & XDM a rapid Refresh/Clone/TDM Suite for Db2 z & distributed.
DBARS -Audit,record,& block Db2 accesses to sensitive data real-time, NO audit trace or log required
http://www.ESAIGroup.com/IDUG



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Anantha kumar Bysani, Mr.

RE: Timestamp calculation
(in response to António Barata)

As Larry mentioned there are several ways.

1.You could DELETE rows using DATE function. WHERE DATE(TIMESTAMP) = CURRENT DATE - 1 DAY

2. Another method REORG DISCARD. During the DISCARD you could save DISCARDED rows in the file. To discard rows we could use below cretiria.

WHEN DATE(TIMESTAMP) = CURRENT DATE - 1 DAY