Get Data in UTC Format

[abdullah123456789]

When i tried to export data in csv format i get data in local time of my computer but I need to get time information in UTC format for csv file extension. Could you help me about this situation?

I also attached example file at bottom.

i am sharing the SQL query i am using.

SELECT
FORMATVALUE( "CurrentTime" USING 'yyyy-MM-dd HH:mm:SS') as "~ Record Time", "WLM_ID","CurrentValueAsReal", "CurrentQuality"

FROM
CDBPoint

WHERE
"WLM_ID" <> ''

[CraigEl]

What Product does this question relate to?

[abdullah123456789]

I get this problem in Geo SCADA Expert 2021.

[AndrewScott]

You've not specified how you're doing the export, but I assume you're using the SQL export driver. rather than a custom application or Microsoft Excel.

The SQL export driver doesn't allow the time zone to be specified. For 'raw' times it outputs them in UTC, for times converted to strings the query processor converts them to local time during the conversion, and then returns the resulting text to the SQL export driver.

The following query should return the times in UTC but using the default formatting:

SELECT
    "CurrentTime", "WLM_ID", "CurrentValueAsReal", "CurrentQuality"
FROM
    CDBPoint
WHERE
    "WLM_ID" <> ''

[abdullah123456789]

Thanks for the help. It worked.

Additionally, i am getting the data as in this screenshot,

but i need to get the data as in this screenshot.

In summary i need to get data as YYYY-MM-DD HH:MM:SS

Time format should be in UTC, date format should be as in the second screenshot

[AndrewScott]

The format of values in the CSV file is determined by the locale that is specified for the SQL export:

Specify the Locale that Geo SCADA Expert is to Apply to the Exported SQL Query Result 

I think Polish and Swedish both uses a date-time format of "yyyy-MM-dd HH:mm:ss.SSS"

[abdullah123456789]

It worked. Thanks for your support.

[abdullah123456789]

Hello,

I'm having a timezone issue while exporting data from Scada. I'm not sure if it's a problem to continue here, but the issue I'm experiencing is related to the timezone.

I'm using Geo Scada Expert 2023, November 2024. I created an SQL query to retrieve my field data. I'm filtering the data based on this SQL query and exporting it to a file path on the server as a CSV file every 15 minutes. However, when I examine the CSV file I've imported, the data is saved in UTC, not local time. However, my local time is UTC +3, and the server time is also set accordingly. Furthermore, when I view the historical data list in Scada, the data is displayed in my local time, not UTC. Could you help me resolve this issue and save the CSV files in local time as well?

Although the CSV file name is saved with the correct time as shown in the first image, when I open the file, I see the image directly below it.

My settings in the SQL Query screen are as follows.

I've also configured the general settings in Scada accordingly.

[AdamWoodlandToo]

You've not shared your SQL query so I am making some assumptions here.

If you've copied Andrew's query above and using CurrentTime he mentioned it only uses UTC in its queries (unless you cast it), whereas when you view the data in ViewX it will use the setting on your user account on whether to show date/time as UTC or not.

Generally using UTC is better as it avoids the complexities of daylight saving, however if your system doesn't use daylight saving or you're in a country that doesn't use it, could you just hardcode it and use TIMESTAMPADD and add 3 hours as part of the query? I'd recommend you output both UTC and any "converted" time as columns in the output as any ingestion of the data down the line might prefer UTC too.

Reusing the example in the help under "Expressions.expr-primary-odbc-timestampadd" doing something similar to below might work?

SELECT {FN TIMESTAMPADD(SQL_TSI_HOUR, 3, CurrentTime)} FROM CDBPoint

[abdullah123456789]

I'm sorry. This is the SQL query I used. You can make a more accurate comment based on this query.

SELECT

"His15MAvg"."Time" AS "CurrentTime", "CDBPoint"."WLM_ID", "His15MAvg"."Value" AS "CurrentValueAsReal", "CDBPoint"."CurrentQuality"

FROM

"CDBPoint" CROSS JOIN "His15MAvg"

WHERE

"CDBPoint"."WLM_ID" <> '' AND "CDBPoint"."Id" = "His15MAvg"."Id" AND ( "His15MAvg"."Time" BETWEEN { OPC 'M -15M' } AND { OPC 'M' } ) AND "CDBPoint"."WLM_ID" IS NOT NULL AND "His15MAvg"."Time" IS NOT NULL AND "His15MAvg"."Value" IS NOT NULL AND ( "CDBPoint"."WLM_ID" LIKE '%Anlik%' )

ORDER BY

"WLM_ID" ASC