pink cylinders

More than Four

...there's an axis for that.

Upgrading site from BlogEngine 2.5 to 3.3

I though this was going to be a nightmare, but getting the content moved from 2.5 to 3.3 was the easiest part, using the BlogML export and import. The tricky part was adapting the Standard theme to match my previously customized one. A straight copy of the theme folder did not work.

This will do for now.

Flyway DB

For years I've used a database build script framework written in-house to recompile all schema objects and run unit tests. However, the actual DDL code to migrate a schema from one version to the next was always left as a hand-rolled script that is executed up-front before the normal build process.

A colleague introduced me to Flyway DB and I am now a fan. The introduction, documentation, and tutorial material is top-notch.

Installing Flyway on Linux

Flyway DB can be found here:

Although it has an API for integration, I specifically like the command-line utility version:  https://flywaydb.org/getstarted/firststeps/commandline

Unzip the command-line tool version into your preferred program location. In my Linux VM, I decided on ~/bin:

image

and created a simlink to put flyway into the path:

$ ln -s ~/bin/flyway-4.2.0/flyway ~/bin/flyway

Now I can run it from any directory.

How Flyway works

Flyway scans the contents of a “migration scripts” folder using a file naming convention to understand which scripts belong to which specific version.

It then uses a special table in the target database called “schema_version” that contains metadata about the current version of the database.

Using Flyway

The basic syntax is

> flyway VERB

where VERB is one of:

  • migrate
  • clean
  • info
  • validate
  • baseline
  • repair

In our case, we need to:

  • "baseline" our database so that Flyway understands the current starting "version";
  • write a DDL script to move the DB to the next version;
  • "migrate" our database instance to that version.

Setting up a working project directory

  • Create a working folder
  • copy the {install}/conf/flyway.conf file into the working directory, for project-specific overrides;
  • create a sub-folder called sql

The working directory I set up was:

/home/colin/Projects/flyway
/home/colin/Projects/flyway/sql

I copied the flyway.conf file, ready for local configuration.

Selecting the target DB

I've been working through the "MySQL Cookbook" tutorial, so I have a user and a database installed in the VM locally, called "cookbook", with one table "limbs" already created.

Editing the .conf file:

flyway.url=jdbc:mysql://localhost:3306/cookbook
flyway.user=root
flyway.locations=filesystem:./sql

We could supply the password as well, but for interactive use it is safer to have flyway prompt for it. For automated processes, you'd want to put the password in the .conf file.

Note the syntax of specifying the location of the SQL migration scripts, with a "filesystem:" prefix.

Just for kicks, let's run the "migrate" command:

$ flyway migrate
Flyway 4.2.0 by Boxfuse

Database password: ****
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
Successfully validated 0 migrations (execution time 00:00.003s)
ERROR: Found non-empty schema(s) `cookbook` without metadata table! Use baseline() or set baselineOnMigrate to true to initialize the
$

We see a couple of issues:

  • the target DB is missing the metadata table, and therefore Flyway doesn't know what version it is;
  • we don't have any upgrade scripts to process.

Baselining our target DB

We can update the .conf file to include information about the database. Let's assume we are starting at version 5:

flyway.baselineVersion=5
flyway.baselineDescription=Baseline prior to Flyway

Now we can use the "baseline" command to create the metadata:

$ flyway baseline
Flyway 4.2.0 by Boxfuse

Database password: ****
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
Creating Metadata table: `cookbook`.`schema_version`
Successfully baselined schema with version: 5
$

Querying MySQL shows the table now exists:

mysql> show tables;
+--------------------+
| Tables_in_cookbook |
+--------------------+
| limbs |
| schema_version |
+--------------------+
2 rows in set (0.00 sec)

And the contents:

mysql> select * from schema_version;
+----------------+---------+--------------------------+----------+--------------------------+----------+--------------+--------------
| installed_rank | version | description | type | script | checksum | installed_by | installed_on
+----------------+---------+--------------------------+----------+--------------------------+----------+--------------+--------------
| 1 | 5 | Baseline prior to Flyway | BASELINE | Baseline prior to Flyway | NULL | root | 2017-06-12 14
+----------------+---------+--------------------------+----------+--------------------------+----------+--------------+--------------
1 row in set (0.00 sec)

We can use the "info" verb on the command-line to review the current version according to Flyway:

$ flyway info
Flyway 4.2.0 by Boxfuse

Database password: ****
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
+---------+--------------------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+--------------------------+---------------------+---------+
| 5 | Baseline prior to Flyway | 2017-06-12 14:23:41 | Baselin |
+---------+--------------------------+---------------------+---------+
$
 
Both schema and table name can be changed in the .conf, but it is probably better to leave it at default. For testing migrations on a freshly-built test database, there is a setting in the .conf that allows automatic default baselining if the schema table is not detected:
 
flyway.baselineOnMigrate=true

This can be very handy, along with specifying the DB user password, if security is not a priority.

Migrating the DB to a new version

Our current DB version is 5, but I'm going to create scripts for the versions either side of that, in order to see what Flyway does.

The script filenaming convention is documented here:

There are a number of possibilities including versioning scripts; repeatable scripts; and SQL callback scripts for invoking at various points during the process. For now, we're just looking at versioning scripts.

Consider the following script files:

$ ls -l sql/*.sql
-rw-rw-r-- 1 colin colin 64 Jun 12 14:35 sql/V4__other_objects.sql
-rw-rw-r-- 1 colin colin 80 Jun 12 14:35 sql/V5__limbs.sql
-rw-rw-r-- 1 colin colin 62 Jun 12 14:36 sql/V6__test_table2.sql
$

Note: it may be tricky to remember that the name convention requires a double-underscore ("dunder") between the version number and the file name!

Now, if we run the "info" verb:

Flyway 4.2.0 by Boxfuse
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)

+---------+--------------------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+--------------------------+---------------------+---------+
| 4 | other objects | | <Baseln |
| 5 | Baseline prior to Flyway | 2017-06-12 14:23:41 | Success |
| 6 | test table2 | | Pending |
+---------+--------------------------+---------------------+---------+
$

Flyway detects this information from a) the script file naming convention, and b) the contents of the schema_version table in the target DB.

Questions:

  • Can we have more than one script belonging to a specific version?
  • If so, what is the execution sequence?
  • Can we have sub-versions (e.g. 5.1, 5.2 etc)?

The answer is that Flyway will throw an error if it detects two scripts associated with the same version number, so in fact you MUST use sub-version numbers for multiple scripts, like this:

V6_0__test_table2.sql
V6_1_1_test_table1.sql
V6_1_2__test_table3.sql

Of course, this now dictates the execution sequence:

$ flyway info
Flyway 4.2.0 by Boxfuse
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
+---------+--------------------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+--------------------------+---------------------+---------+
| 4 | other objects | | <Baseln |
| 5 | Baseline prior to Flyway | 2017-06-12 14:23:41 | Success |
| 6.0 | test table2 | | Pending |
| 6.1.1 | test table1 | | Pending |
| 6.1.2 | test table3 | | Pending |
+---------+--------------------------+---------------------+---------+

Now, let's migrate!

$ flyway migrate
Flyway 4.2.0 by Boxfuse

Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
Successfully validated 5 migrations (execution time 00:00.018s)
Current version of schema `cookbook`: 5
Migrating schema `cookbook` to version 6.0 - test table2
Migrating schema `cookbook` to version 6.1.1 - test table1
Migrating schema `cookbook` to version 6.1.2 - test table3
Successfully applied 3 migrations to schema `cookbook` (execution time 00:00.115s).
$

mysql> show tables;
+--------------------+
| Tables_in_cookbook |
+--------------------+
| limbs |
| schema_version |
| test1 |
| test2 |
| test3 |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from schema_version;
+----------------+---------+--------------------------+----------+--------------------------+-------------+--------
| installed_rank | version | description | type | script | checksum | install
+----------------+---------+--------------------------+----------+--------------------------+-------------+--------
| 1 | 5 | Baseline prior to Flyway | BASELINE | Baseline prior to Flyway | NULL | root
| 2 | 6.0 | test table2 | SQL | V6_0__test_table2.sql | 224077121 | root
| 3 | 6.1.1 | test table1 | SQL | V6_1_1__test_table1.sql | 669488585 | root
| 4 | 6.1.2 | test table3 | SQL | V6_1_2__test_table3.sql | -1574979834 | root
+----------------+---------+--------------------------+----------+--------------------------+-------------+--------
4 rows in set (0.00 sec)

And now:
 
$ flyway info
Flyway 4.2.0 by Boxfuse

Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
+---------+--------------------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+--------------------------+---------------------+---------+
| 4 | other objects | | <Baseln |
| 5 | Baseline prior to Flyway | 2017-06-12 14:23:41 | Success |
| 6.0 | test table2 | 2017-06-12 15:01:22 | Success |
| 6.1.1 | test table1 | 2017-06-12 15:01:22 | Success |
| 6.1.2 | test table3 | 2017-06-12 15:01:22 | Success |
+---------+--------------------------+---------------------+---------+

It's pretty cool!

Installing Oracle's JDK in Ubuntu Linux

Just for my own notes, because I can never remember this stuff.

1. Check the architecture of your host:

colin@Mongpy:~$ uname -m
x86_64

2. Grab the appropriate download from Oracle's web site:

image

My web browser puts it in the Downloads folder under home.

colin@Mongpy:~$ ls -l ~/Downloads
total 393356
drwxr-sr-x 7 colin colin 4096 Mar 31 12:21 eclipse-installer
-rw-rw-r-- 1 colin colin 185540433 May 25 13:19 jdk-8u131-linux-x64.tar.gz
-rw-rw-r-- 1 colin colin 21944320 Mar 29 14:18 mysql-workbench-community-6.3.9-1ubuntu16.10-amd64.deb
-rw-rw-r-- 1 colin colin 195297254 May 25 12:47 pycharm-community-2017.1.3.tar.gz

3. Open a terminal and get superuser access:

colin@Mongpy:~$ sudo su
[sudo] password for colin:
root@Mongpy:/home/colin#

4. Make a directory and expand the archive:

root@Mongpy:/home/colin# mkdir /opt/jdk
root@Mongpy:/home/colin# tar -zxf ./Downloads/jdk-8u131-linux-x64.tar.gz -C /opt/jdk
root@Mongpy:/home/colin# ls -l /opt/jdk
total 4
drwxr-xr-x 8 uucp 143 4096 Mar 15 01:35 jdk1.8.0_131

5. Review the current default JDK:

root@Mongpy:~# update-alternatives --display java
java - auto mode
link best version is /usr/lib/jvm/java-8-openjdk-amd64/jre/bin/java
link currently points to /usr/lib/jvm/java-8-openjdk-amd64/jre/bin/java
link java is /usr/bin/java
slave java.1.gz is /usr/share/man/man1/java.1.gz
/usr/lib/jvm/java-8-openjdk-amd64/jre/bin/java - priority 1081
slave java.1.gz: /usr/lib/jvm/java-8-openjdk-amd64/jre/man/man1/java.1.gz

root@Mongpy:~# update-alternatives --display javac
javac - auto mode
link best version is /usr/lib/jvm/java-8-openjdk-amd64/bin/javac
link currently points to /usr/lib/jvm/java-8-openjdk-amd64/bin/javac
link javac is /usr/bin/javac
slave javac.1.gz is /usr/share/man/man1/javac.1.gz
/usr/lib/jvm/java-8-openjdk-amd64/bin/javac - priority 1081
slave javac.1.gz: /usr/lib/jvm/java-8-openjdk-amd64/man/man1/javac.1.gz

Note the current "priority" is 1081.

6. Set the Oracle version as the default JDK, using a higher priority, say 1090:

root@Mongpy:/home/colin# update-alternatives --install /usr/bin/java java /opt/jdk/jdk1.8.0_131/bin/java 1090

update-alternatives: using /opt/jdk/jdk1.8.0_131/bin/java to provide /usr/bin/java (java) in auto mode

root@Mongpy:/home/colin# update-alternatives --install /usr/bin/javac javac /opt/jdk/jdk1.8.0_131/bin/javac 1090

update-alternatives: using /opt/jdk/jdk1.8.0_131/bin/javac to provide /usr/bin/javac (javac) in auto mode

root@Mongpy:/home/colin# update-alternatives --display java
java - auto mode
link best version is /opt/jdk/jdk1.8.0_131/bin/java
link currently points to /opt/jdk/jdk1.8.0_131/bin/java
link java is /usr/bin/java
slave java.1.gz is /usr/share/man/man1/java.1.gz
/opt/jdk/jdk1.8.0_131/bin/java - priority 1090
/usr/lib/jvm/java-8-openjdk-amd64/jre/bin/java - priority 1081
slave java.1.gz: /usr/lib/jvm/java-8-openjdk-amd64/jre/man/man1/java.1.gz

root@Mongpy:/home/colin# update-alternatives --display javac
javac - auto mode
link best version is /opt/jdk/jdk1.8.0_131/bin/javac
link currently points to /opt/jdk/jdk1.8.0_131/bin/javac
link javac is /usr/bin/javac
slave javac.1.gz is /usr/share/man/man1/javac.1.gz
/opt/jdk/jdk1.8.0_131/bin/javac - priority 1090
/usr/lib/jvm/java-8-openjdk-amd64/bin/javac - priority 1081
slave javac.1.gz: /usr/lib/jvm/java-8-openjdk-amd64/man/man1/javac.1.gz

7. Finally, testing:

colin@Mongpy:~$ java -version
java version "1.8.0_131"
Java(TM) SE Runtime Environment (build 1.8.0_131-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.131-b11, mixed mode)

Cool.

MySQL notes

The situation is that I have a MySQL database instance somewhere out on the network. And I'm trying to connect to it.

Logging in from the DB host

Plan:

  • Open a terminal window on the DB host, 192.168.1.130
  • Attempt to connect as the root db user
[root@zyxx01 ~]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

That didn't work. This does, though:
 
[root@zyxx01 ~]# mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 171310
Server version: 5.5.32-log MySQL Community Server (GPL)

mysql>

So, don't forget the -p option on the command-line. I guess that it tries first to connect without a password, which is a possible configuration for MySQL. And fails because it isn't allowed in this instance.

Logging in from remote machine

My first attempt here didn't work either, because "mysql" actually runs a batch file which expands into:

D:\>c:\progra~1\mysql\mysqls~1.5\bin\mysql.exe --defaults-file=c:\ProgramData\MySQL\mysqls~1.5\my.ini -uroot -p
Enter password: ********
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

Invoking the .exe directly avoids that interesting "defaults-file" thing:
D:\>c:\progra~1\mysql\mysqls~1.5\bin\mysql.exe -h 192.168.1.130 -u root -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 204151
Server version: 5.5.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Cool, we're in. So now, let's create a user:

mysql> create user 'abacab'@'%' identified by '****';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'abacab'@'%' ;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

Testing the user:

D:\>c:\progra~1\mysql\mysqls~1.5\bin\mysql.exe -h 192.168.1.130 -u abacab -p
Enter password: ***********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 206648
Server version: 5.5.32-log MySQL Community Server (GPL)
...
mysql>

Success.

Finding the Data Dictionary in DB2

I'm not creating a special "DB2" category for this post :-)

I've been looking for metadata, or some kind of data dictionary similar to those available in MS SQL Server or Oracle DB, to describe the tables and columns in a schema in DB2.

The closest match so far seems to be information in the SYSIBM library:

Specifically:

  • SQLCOLUMNS
  • SQLTABLES

Reference:

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.

Setting up a LAMP test environment

Not my usual kind of thing, but a possible new client meant preparing a system to stand up a replica of their production environment. These are my notes.

I started by downloading an .iso of ubuntu-12.04-server and deploying it to a new VM.

Critical things to remember:

  • Set up two network adapters in the virtual machine config: NAT, and Host-only networking, as per my notes documented elsewhere;
  • select [X] LAMP server during the deployment to give it Apache/MySQL/PHP in one hit, at startup;
  • install ssh so you can remote into it
$ sudo apt-get install openssh-server openssh-client

Checking for installed status:

$ which php
/usr/bin/php

$ sudo service apache2 status
[sudo] password for colin: ****
Apache2 is running (pid 1107).

$ mysql -u root -p
Enter password: ****
..
mysql>

By default, the Apache web server document root folder appears to be:

$ ls -l /var/www
total 4
-rw-r--r-- 1 root root 177 Dec 6 12:10 index.html
$

Allowing connections to MySQL from outside the server

Edit /etc/mysql/my.conf:

Comment out the line:

#bind-address    = 127.0.0.1

Run the following SQL(might not be required, note the 0 rows affected):

mysql> grant all privileges on *.* to 'root'@'%' identified by '****' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Restart the DB:

$ sudo service mysql stop
mysql stop/waiting

$ sudo service mysql start
mysql start/running, process 1578

And, importantly, test from outside (in my case, my host operating system):

C:\Program Files\MySQL\MySQL Server 5.5\bin> mysql -h 192.168.56.56 -u root -p
Enter password: ****
...
mysql>

In MySQL Workbench:

image

Cool.

Installing FTP server

Just in case we need to move files in and out of the test environment (which we almost certainly will need to do), we'll set up an FTP server. The most suggested option seems to be something called vsftpd:

Reference:  https://www.liquidweb.com/kb/how-to-install-and-configure-vsftpd-on-ubuntu-12-04-lts/

When I tried this, I got an error:

$ sudo apt-get install vsftpd
[sudo] password for colin: ***
Reading package lists... Done
Building dependency tree
Reading state information... Done
Package vsftpd is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source

E: Package 'vsftpd' has no installation candidate

Long story short, the following sequence of instructions worked for me:

1. un-comment the extras.ubuntu.com repositories in /etc/apt/sources.list:

## Uncomment the following two lines to add software from Ubuntu's
## 'extras' repository.
## This software is not part of Ubuntu, but is offered by third-party
## developers who want to ship their latest software.
deb http://extras.ubuntu.com/ubuntu precise main
deb-src http://extras.ubuntu.com/ubuntu precise main

2. Remove old information, and update:

$ sudo rm -rf /var/lib/apt/lists/*
$ sudo apt-get update

3. try installing again:

$ sudo apt-get install vsftpd
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
vsftpd
0 upgraded, 1 newly installed, 0 to remove and 129 not upgraded.
Need to get 124 kB of archives.
After this operation, 342 kB of additional disk space will be used.
Get:1 http://us.archive.ubuntu.com/ubuntu/ precise/main vsftpd amd64 2.3.5-1ubuntu2 [124 kB]
Fetched 124 kB in 1s (91.2 kB/s)
Preconfiguring packages ...
Selecting previously unselected package vsftpd.
(Reading database ... 53560 files and directories currently installed.)
Unpacking vsftpd (from .../vsftpd_2.3.5-1ubuntu2_amd64.deb) ...
Processing triggers for man-db ...
Processing triggers for ureadahead ...
Setting up vsftpd (2.3.5-1ubuntu2) ...
vsftpd start/running, process 2229
$

Thank goodness for that!

Some configuration required in /etc/vsftpd.conf, see:

basically

write_enable=YES
local_umask=022

Restart:

$ sudo service vsftpd restart
vsftpd stop/waiting
vsftpd start/running, process 2462
$

Now, testing from the Windows host:

C:\PROGRA~2\WinSCP>WinSCP
winscp> open 192.168.56.56
Searching for host...
Connecting to host...
Authenticating...
Username: colin
Password: ****
Authenticated.
Starting the session...
Session started.
Active session: [1] 192.168.56.56
winscp>

OK, I think it is all ready for us to unpack that archive we got from the client and see about deploying into the test environment.

Dates and Time Zone conversion

It's time to take another look at some date/time conversion techniques...

Consider reading a DATE value from some source system, in a known time zone, and needing to convert it into some other time zone before saving into the destination table:

with
src_date as (
select
-- ---------------------------------------------------------------------
-- Here's a date value from the source system. From the value itself
-- we can't tell anything significant about it:
-- ---------------------------------------------------------------------
to_date('2016-10-03 23:20','yyyy-mm-dd hh24:mi') as INPUT_DT
from dual
),
assume_data as (
select
INPUT_DT,
-- --------------------------------------------------------------------------
-- If we know the value is a US/Pacific time zone value, we can declare that:
-- --------------------------------------------------------------------------
from_tz( cast( INPUT_DT as TIMESTAMP) , 'US/Pacific' ) as PACIFIC_TSTZ,
-- --------------------------------------------------------------------------
-- Alternatively, what if we know it is in China Standard Time ?
-- --------------------------------------------------------------------------
from_tz( cast( INPUT_DT as TIMESTAMP) , '+8:00' ) as CHINA_TSTZ
from src_date
)
select
-- ----------------------------------------------------------------
-- Now we have values that declare what time zone they belong to,
-- we can easily transform the value into datetimes for a different time zone:
-- ----------------------------------------------------------------
INPUT_DT,
PACIFIC_TSTZ,
CHINA_TSTZ,
-- ----------------------------------------------------------------
-- convert the China Standard Time value into US/Central:
-- ----------------------------------------------------------------
CHINA_TSTZ at time zone 'US/Central' as CENTRAL_TSTZ ,
-- ----------------------------------------------------------------
-- convert the US/Pacific time into UTC:
-- ----------------------------------------------------------------
PACIFIC_TSTZ at time zone 'UTC' as UTC_TSTZ,
PACIFIC_TSTZ at time zone '+0:00' as UTC_TSTZ_ALT
from assume_data;

Results:

image

For example, when loading DATE values from C2R (Pacific time) and storing them in EDW (UTC) we could use the following expression:

cast( from_tz(cast( INPUT_DT as TIMESTAMP), 'US/Pacific') at time zone 'UTC' as DATE )

That is, if we really needed a DATE data type. I believe Oracle DB would handle the outer cast automatically if we attempted to save the TIMESTAMP+TZ value into a destination table's DATE column

For more fun: