Timestamps returned from database is coverting to UTC

[Priya Jayaprakash]

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?

<CFQUERY NAME="checkForFiles" DATASOURCE="#Application.DataSource#">

     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)

</CFQUERY>

<cfdump var=#checkForFiles#>

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

[Igal @ Lucee.org]

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

--
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+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/81cc2665-eac8-4495-9604-52d3bcf953c4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Priya Jayaprakash]

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'