Wed, 07 Jun 2006

Converting Epoch Time values To Timestamps

Just a quick one. If you've ever created a table using the number of seconds since 1970 and realised, after populating it with data, that you really need it in a TIMESTAMP type? If so, you can quickly convert it using this SQL:

ALTER TABLE entries 
   ALTER COLUMN created TYPE TIMESTAMP WITH TIME ZONE
      USING TIMESTAMP WITH TIME ZONE 'epoch' + created *interval '1 second';

With thanks to the PostgreSQL manual for saving me hours working out how to do this.

[, ] | # Read Comments (1) |

Comments

Oracle 10.2.0.1 Instant Client hanging?

Does your Oracle client hang when connecting? Are you using Oracle 10.2.0.1? Do you get the following if you strace the process?

gettimeofday({1129717666, 622797}, NULL) = 0
access("/etc/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("./network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/etc/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("./network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
fcntl64(155815832, F_SETFD, FD_CLOEXEC) = -1 EBADF (Bad file descriptor)
times(NULL) = -1808543702
times(NULL) = -1808543702
times(NULL) = -1808543702
times(NULL) = -1808543702
times(NULL) = -1808543702
times(NULL) = -1808543702
times(NULL) = -1808543702
.
.
.

Has your client been up for more that 180 days? Well done; you've just come across the same bug that has bitten two of our customers in the last week. Back in the days of Oracle 8, there was a fairly imfamous bug in the Oracle client where new connections would fail if the client had been up for 248 days or more. This got fixed, and wasn't a problem with Oracle 9i at all. Now Oracle have managed to introduce a similar bug in 10.2.0.1, although in my experience the number of days appears to be shorter (180+).

Thankfully, this has been fixed in the 10.2.0.2 Instant Client. More information can be found on forums.oracle.com and www.redhat.com.

[, ] | # Read Comments (1) |

Comments