Areas of Interest, as counted by my cat

Month: January 2017

Selecting a timestamp AT TIME ZONE – a gotcha

So there’s this Oracle database instance in the midwest, in US/Central time zone.

So if I use PuTTY to open a terminal window on the host and open a SQL*Plus session, if I execute this statement I’ll get the following results:

SQL> select
max( CHANGE_TS ) as MAX_CHANGE,
max( CHANGE_TS at time zone 'US/Central' ) as MAX_CST_CHANGE
from tRetired_Mst;

MAX_CHANGE MAX_CST_CHANGE
-------------------------------- ---------------------------------------------
05-JAN-17 02.33.17.613000 PM 05-JAN-17 02.33.17.613000 PM US/CENTRAL

As expected, they are the same, because the local time zone of the session (terminal window) is the same as the DB, US/Central.

However:

If I run PL/SQL Developer on my Windows Laptop here in California, and connect to the same Oracle instance, then the results are different:

SQL> select
max( CHANGE_TS ) as MAX_CHANGE,
max( CHANGE_TS at time zone 'US/Central' ) as MAX_CST_CHANGE
from tRetired_Mst;

MAX_CHANGE MAX_CST_CHANGE
----------------------------- ----------------------------------------------
2017-01-05 14:33:17 05-JAN-17 04.33.17.613000 PM US/CENTRAL

Observe thatthe MAX_CST_CHANGE value is off by two hours! Because the timestamp value was evaluated as if it were in the local session time of my laptop, which is US/Pacific.

For code executing in PL/SQL packages in the database, this is probably not an issue… but if you are using this technique to convert values in ad-hoc SQL, you should be aware of this trap.

Tagging with Git

This is newbie-level stuff, but it is new to me…

When I used TortoiseGit to tag my latest changes, I got a strange error “Directory C:\Documents does not exist.”

image

Rather than spend time tracking this down, I decided to bite the bullet and figure out how to use the command line. It’s not hard (of course), you just have to know and remember.

Reference:

Here’s what I did:

Change to the project directory:

> cd \source_control\ABACAB\github\zyxx\zyxx-db\master

Review the current tags (just for fun):

D:\..\zyxx-db\master> git tag
20150614_clean_1
dev_2015_02_A_1
zyxx-1_0_0
zyxx-1_0_1
zyxx-1_0_2
zyxx-41_1-prod
zyxx-prod-42
:
zyxx-prod-50
zyxx-prod-54
zyxx-uat-20150423

D:\..\zyxx-db\master>

Ah, consistency: the hobgoblin of little minds. Or something like that.

Now create a new annotated tag:

D:\..\zyxx-db\master> git tag -a zyxx-prod-55 -m "For Jan 5 2017 deployment to PROD"
D:\..\zyxx-db\master>

We have to explicitly push the tag to the remote server:

D:\..\zyxx-db\master> git push origin zyxx-prod-55
Counting objects: 1, done.
Writing objects: 100% (1/1), 183 bytes | 0 bytes/s, done.
Total 1 (delta 0), reused 0 (delta 0)
To https://github.abacab.com/zyxx/zyxx-db.git
* [new tag] zyxx-prod-55 -> zyxx-prod-55

D:\..\zyxx-db\master>

…and I think that’s it.

© 2024 More Than Four

Theme by Anders NorenUp ↑