Timestamps returned from database is coverting to UTC

dev google-group-archive
1

OS: Windows Server 2008 R2 SP1 - timezone PST
Database: Teradata 15.0 - timezone PST
Datasource: Other JDBC Driver
Lucee version 4.5.1.000
Lucee Locale: English (United States)
Lucee Server and Web instances - timezone “PST8PDT - Pacific Standard Time”

Lucee seems to be converting my database timestamps to UTC. The database
contains data with the correct timestamp; for example, 6/11/2015 14:54:50.
However, when doing a cfquery(SELECT) the timestamp returned isn’t correct;
for example,
{ts ‘2015-06-11 22:54:50’}. I’ve attempted changing the datasource timezone
from ‘Same as Lucee instance’ to ‘PST8PDT’ but issue still persists. This
is a major problem if timestamps from the database are auto converted. Is
anyone else facing this issue? Is there a workaround/setting that I’m
missing?

LOCK ROW FOR ACCESS SELECT CURRENT_TIMESTAMP as db_current_ts, Uploaded_TS as doc_uploaded_ts, CAST (Uploaded_TS AS VARCHAR(32)) AS doc_uploaded_ts_casted_varchar FROM falt_db.Dpr_Form_Attachments WHERE Tracking_Number in (1421, 1423)

Output:
Query
Execution Time: 42 ms
Record Count: 4
Cached: No
Lazy: No
SQL:
LOCK ROW FOR ACCESS
SELECT
CURRENT_TIMESTAMP as db_current_ts,
Uploaded_TS as doc_uploaded_ts,
CAST (Uploaded_TS AS VARCHAR(32)) AS doc_uploaded_ts_casted_varchar
FROM falt_db.Dpr_Form_Attachments
WHERE Tracking_Number in (1421, 1423)
db_current_ts doc_uploaded_ts doc_uploaded_ts_casted_varchar 1 {ts
‘2015-06-24 08:09:31’} {ts ‘2015-06-17 19:59:05’} 2015-06-17 11:59:05 2 {ts
‘2015-06-24 08:09:31’} {ts ‘2015-06-16 18:36:31’} 2015-06-16 10:36:31 3 {ts
‘2015-06-24 08:09:31’} {ts ‘2015-06-17 20:00:06’} 2015-06-17 12:00:06 4 {ts
‘2015-06-24 08:09:31’} {ts ‘2015-06-17 22:32:26’} 2015-06-17 14:32:26

2

in the Lucee Admin, when you set up a datasource, there is a setting for
Timezone. what have you got there?

Igal Sapir
Lucee Core Developer
Lucee.org http://lucee.org/On 6/24/2015 8:17 AM, Priya Jayaprakash wrote:

OS: Windows Server 2008 R2 SP1 - timezone PST
Database: Teradata 15.0 - timezone PST
Datasource: Other JDBC Driver
Lucee version 4.5.1.000
Lucee Locale: English (United States)
Lucee Server and Web instances - timezone “PST8PDT - Pacific Standard
Time”

Lucee seems to be converting my database timestamps to UTC. The
database contains data with the correct timestamp; for example,
6/11/2015 14:54:50. However, when doing a cfquery(SELECT) the
timestamp returned isn’t correct; for example,

{ts ‘2015-06-11 22:54:50’}. I’ve attempted changing the datasource
timezone from ‘Same as Lucee instance’ to ‘PST8PDT’ but issue still
persists. This is a major problem if timestamps from the database are
auto converted. Is anyone else facing this issue? Is there a
workaround/setting that I’m missing?

|

LOCK ROW FOR ACCESS
SELECT
CURRENT_TIMESTAMP as db_current_ts,
Uploaded_TS as doc_uploaded_ts,
CAST (Uploaded_TS AS VARCHAR(32)) AS
doc_uploaded_ts_casted_varchar
FROM falt_db.Dpr_Form_Attachments
WHERE Tracking_Number in (1421, 1423)

|

Output:
|
Query
Execution Time: 42 ms
Record Count: 4
Cached: No
Lazy: No
SQL:
LOCK ROW FOR ACCESS
SELECT
CURRENT_TIMESTAMP as db_current_ts,
Uploaded_TS as doc_uploaded_ts,
CAST (Uploaded_TS AS VARCHAR(32)) AS doc_uploaded_ts_casted_varchar
FROM falt_db.Dpr_Form_Attachments
WHERE Tracking_Number in (1421, 1423)
db_current_ts doc_uploaded_ts doc_uploaded_ts_casted_varchar
1 {ts ‘2015-06-24 08:09:31’} {ts ‘2015-06-17 19:59:05’} 2015-06-17
11:59:05
2 {ts ‘2015-06-24 08:09:31’} {ts ‘2015-06-16 18:36:31’} 2015-06-16
10:36:31
3 {ts ‘2015-06-24 08:09:31’} {ts ‘2015-06-17 20:00:06’} 2015-06-17
12:00:06
4 {ts ‘2015-06-24 08:09:31’} {ts ‘2015-06-17 22:32:26’} 2015-06-17
14:32:26

|

–
You received this message because you are subscribed to the Google
Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send
an email to lucee+unsubscribe@googlegroups.com
mailto:lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com
mailto:lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/81cc2665-eac8-4495-9604-52d3bcf953c4%40googlegroups.com
https://groups.google.com/d/msgid/lucee/81cc2665-eac8-4495-9604-52d3bcf953c4%40googlegroups.com?utm_medium=email&utm_source=footer.
For more options, visit https://groups.google.com/d/optout.

3

I’ve tried setting the datasource timezone to ‘PST’, ‘PST8PDT’, and ‘UTC’.
But the issue still persists. Right now I’ve reverted back to ‘Same as
Lucee instance’ - since my Lucee Server and Web timezone is ‘PST8PDT’