time zone queries

[mnguyenva]

CREATE TABLE table_a
( transaction_id VARCHAR2(30)
, transaction_minutes NUMBER
, transaction_datetime_gmt DATE )

All datetimes are stored in GMT in the table.


To select rows where the datetime is between April 1 and April 30:
SELECT *
FROM table_a
WHERE transaction_datetime_gmt BETWEEN '1-APR-2000' AND TO_DATE('30-APR-2000 23:59:59')

To select rows where the transaction_datetime_gmt (in PST) is between April 1 and April 30:
SELECT *
FROM table_a
WHERE NEW_TIME(transaction_datetime_gmt, 'GMT', 'PST') BETWEEN '1-APR-2000' AND TO_DATE('30-APR-2000 23:59:59')

Now, I want to select rows where the transaction_datetime_gmt (in PST or PDT depending on if it's Daylight
Savings Time) is between April 1 and April 30. For 2000:
PST 1-JAN-2000 to 1-APR-2000 1:59:59
PDT 1-APR-2000 2:00:00 to 29-OCT-2000 1:59:59
PST 30-OCT-2000 2:00:00 to 1-APR-2001 1:59:59

In the below Select statement, how do I substitute v_time_zone with either PST or PDT? With a Decode statement somehow?
SELECT *
FROM table_a
WHERE NEW_TIME(transaction_datetime_gmt, 'GMT', v_time_zone) BETWEEN '1-APR-2000' AND TO_DATE('30-APR-2000 23:59:59')

Thanks :-)

[dknight]

If your app will be used outside of the US, you might want to find an absolute indicator of time.

Daylight Savings Time and Sommerzeit (Summer Time) in Germany differ by one week.

Good luck.