Resolving WinCC TagLogging UTC Timestamp Offset in SQL Queries

Overview

WinCC TagLogging (and Alarm Logging) persist every archive value with a UTC timestamp, independent of the operator station's local time zone and independent of daylight saving time. WinCC's standard controls — OnlineTrendControl, OnlineTableControl, AlarmControl, UserArchiveControl — perform the UTC→local conversion internally so the operator always sees the configured local time. The moment you bypass those controls and read the archive tables directly with T-SQL, OLE DB, ADO.NET, the WinCC ODK, or the Connectivity Pack, you receive raw UTC timestamps. If your local clock is GMT+1 with summer time active (CEST = UTC+2), every row appears 2 hours early; if your local clock is EST (UTC-5), every row appears 5 hours late. This is expected behavior, not a bug, and the conversion is the caller's responsibility.

Root Cause: Why WinCC Stores Timestamps in UTC

The archive engine writes the TimeStamp column (in TagUncompressed, TagCompressed, and the analogous alarm tables) using the system UTC clock. Three engineering reasons drive this decision:

1. Cross-site consistency. Distributed WinCC stations and WinCC/Server–WinCC/Client topologies can span time zones. UTC keeps every station writing the same canonical instant, which makes redundant archives comparable.
2. DST ambiguity. Local time loses uniqueness during the autumn "fall-back" hour (02:00–03:00 in Europe, 01:00–02:00 in the US). UTC eliminates the duplicate-hour problem entirely.
3. Database portability. Plain datetime in SQL Server is a monotonic absolute instant. UTC lets you replicate, back up, and query the database on a server whose OS clock is in a different time zone without remapping rows.

Siemens documents this behavior in the WinCC Information System under the TagLogging → Data Storage and Time Stamp topics, and in the public support FAQ entries Entry ID 17811102, Entry ID 23067556, and Entry ID 23542699.

Affected Versions, Components, and Data Sources

Every direct-access path into the archive returns the same UTC instant:

• Tag Logging RT tables: dbo.TagUncompressed, dbo.TagCompressed, dbo.TagUncompressed_, dbo.TagCompressed_.
• Alarm Logging tables: dbo.MSArcNew, dbo.MSArcConfNew, dbo.MSArcCamNew, dbo.MSEdArcNew.
• Connectivity Pack: CC_OpenArchive via WinCCOLEDBProvider.1 — see WinCC Connectivity Pack V7.5 documentation.
• WinCC ODK: DMGetArchiveTagValues, DMGetArchive, DMGetAlarm — return UTC by contract.

Confirming the UTC Offset with a Diagnostic Query

Before applying any conversion, prove the offset is real and measure it. Open SQL Server Management Studio (or Query Analyzer for legacy sites) against the runtime database CC_<Project>_<YYMMDD>_<HHMMSS>_<NNN> and run the following diagnostic:

-- Replace 7 with the ValueID of your test tag
SELECT TOP 10 
    ut.TimeStamp        AS ArchiveUTC,
    GETUTCDATE()        AS ServerNowUTC,
    GETDATE()           AS ServerLocal,
    DATEDIFF(HOUR, ut.TimeStamp, GETUTCDATE()) AS HourDelta
FROM dbo.TagUncompressed AS ut
WHERE ut.ValueID = 7
ORDER BY ut.TimeStamp DESC;

Compare GETUTCDATE() with GETDATE() — that is the server's current DST-aware local offset. Compare it again with DATEDIFF(HOUR, ut.TimeStamp, GETUTCDATE()) on the most recent row. If both deltas agree, the archive is writing UTC and your offset is the only transformation missing.

SQL Conversion: Local Time from UTC Archive Timestamps

The base conversion is a single DATEADD. The choice of offset depends entirely on the operator station and the date being converted — DST matters (see next section).

-- Fixed-offset conversion (e.g., a non-DST site or a region without DST)
SELECT
    DATEADD(HOUR, +1, tu.TimeStamp) AS TimeStamp_Local,
    tu.RealValue,
    a.ValueName
FROM dbo.TagUncompressed AS tu
JOIN dbo.Archive          AS a  ON a.ValueID = tu.ValueID
WHERE tu.ValueID = 7
ORDER BY tu.TimeStamp DESC;

For a static GMT+1 / CET site without DST, that is the complete fix. For European, North American, and most Asian–Pacific industrial sites you need DST awareness.

Handling Daylight Saving Time (DST) in SQL

DST rules vary by jurisdiction. Two practical strategies:

Strategy 1 — Static DST table

Maintain a small reference table that lists each DST transition and join it onto the archive row. This is the most portable method across SQL Server versions.

CREATE TABLE dbo.DST_Europe_West (
    BoundaryUTC   DATETIME     NOT NULL PRIMARY KEY,
    LocalOffsetH  TINYINT      NOT NULL   -- 1 = CET, 2 = CEST
);
INSERT INTO dbo.DST_Europe_West (BoundaryUTC, LocalOffsetH) VALUES
 ('2023-03-26 01:00:00', 2),  -- CEST starts  
 ('2023-10-29 01:00:00', 1),  -- CEST ends   
 ('2024-03-31 01:00:00', 2),  -- CEST starts  
 ('2024-10-27 01:00:00', 1),  -- CEST ends   
 ('2025-03-30 01:00:00', 2),
 ('2025-10-26 01:00:00', 1);

SELECT
    DATEADD(HOUR, d.LocalOffsetH, tu.TimeStamp) AS TimeStamp_Local,
    tu.RealValue,
    a.ValueName
FROM dbo.TagUncompressed AS tu
JOIN dbo.Archive          AS a  ON a.ValueID  = tu.ValueID
JOIN dbo.DST_Europe_West  AS d  ON tu.TimeStamp >= d.BoundaryUTC
WHERE tu.ValueID = 7
  AND tu.TimeStamp < ISNULL(
        (SELECT MIN(d2.BoundaryUTC) FROM dbo.DST_Europe_West d2
         WHERE d2.BoundaryUTC > d.BoundaryUTC),
        '9999-12-31')
ORDER BY tu.TimeStamp DESC;

Strategy 2 — Use datetimeoffset and AT TIME ZONE (SQL Server 2016+)

If your WinCC site runs V7.5 with SQL Server 2016 or newer, the modern built-in is by far the cleanest.

SELECT
    tu.TimeStamp AT TIME ZONE 'UTC'
                AT TIME ZONE 'Central European Standard Time' AS TimeStamp_Local,
    tu.RealValue,
    a.ValueName
FROM dbo.TagUncompressed AS tu
JOIN dbo.Archive          AS a ON a.ValueID = tu.ValueID
WHERE tu.ValueID = 7
ORDER BY tu.TimeStamp DESC;

Windows time zone names — 'Central European Standard Time', 'Pacific Standard Time', 'Tokyo Standard Time', etc. — are stored in the server's registry under HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. Each entry contains the DST rules that SQL Server uses for AT TIME ZONE. Reference: Microsoft Docs — AT TIME ZONE (Transact-SQL).

Using the WinCC Connectivity Pack (OLE DB)

The WinCC Connectivity Pack exposes the archive through a managed OLE DB provider, WinCCOLEDBProvider.1. A typical T-SQL query against it looks like a normal T-SQL query but the TAG:R (range) or TAG:L (last) prefix activates the WinCC archive query syntax:

SELECT
    DateTime          AS TimeStampUTC,
    RealValue,
    Quality,
    Flags
FROM
    OPENQUERY(
        WinCC_OLE_DB,
        'SELECT * FROM TAG:R(''YourArchive\',''YourTag'',
            ''2024-06-15 00:00:00.000'',''2024-06-16 00:00:00.000'')'
    );

Wrap the returned DateTime with the same DATEADD logic as above:

SELECT
    DATEADD(HOUR, 2, DateTime) AS TimeStampLocal,
    RealValue,
    Quality
FROM OPENQUERY(WinCC_OLE_DB, ...);

For Connectivity Pack configuration prerequisites, see the Connectivity Pack V7.5 SP1 manual. The provider is documented in the WinCC Information System path "Options for Process Control → Connectivity Pack → OLE DB Provider".

VB and VBScript Integration

WinCC scripts that read directly from the archive (e.g., a VBS action on a button click) inherit the same UTC contract. Use the Windows Script Host time-zone object or roll the offset manually.

' VBScript running inside WinCC Graphics Runtime
Dim conn, rs, sql, sLocal
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "WinCCOLEDBProvider.1"
conn.Properties("Data Source").Value = ".\WinCC"
conn.Properties("Catalog").Value = "CC_OpenArchive"
conn.Properties("User ID").Value = "WinCCAdmin"
conn.Properties("Password").Value = "WinCCPass"
conn.Open

sql = "SELECT DateTime, RealValue FROM TAG:R(''ProcessValueArchive''," & _
      "''Tank1.Level'',''2024-06-15 00:00:00.000'',''2024-06-16 00:00:00.000'')"
Set rs = conn.Execute(sql)

Do While Not rs.EOF
    ' Add 2 hours for CEST (1 hour for CET)
    sLocal = DateAdd("h", 2, CDate(rs.Fields("DateTime").Value))
    Trace sLocal & vbTab & rs.Fields("RealValue").Value
    rs.MoveNext
Loop

rs.Close : conn.Close
Set rs = Nothing : Set conn = Nothing

For DST-aware VB conversion, a small helper such as IsInDST(d) that returns the active offset (1 or 2 for Europe) keeps the rest of the code clean.

C# / .NET Integration with WinCC Archives

For OPC UA, custom dashboards, or reporting services that target the WinCC SQL archive, convert in C# using TimeZoneInfo. This avoids the offset-table maintenance entirely.

// C# / .NET Framework 4.8+ example (WinCC V7.5)
using System;
using System.Data.SqlClient;

public static class WinCCArchiveReader
{
    public static DateTime UtcToLocal(DateTime utc)
    {
        // Force DateTimeKind.Utc because SQL Server datetime is kind=Unspecified
        var asUtc = DateTime.SpecifyKind(utc, DateTimeKind.Utc);
        return TimeZoneInfo.ConvertTimeBySystemTimeZoneId(
            asUtc,
            "Central European Standard Time");   // adjust per site
    }

    public static void ReadTankLevel(string connectionString, int valueId)
    {
        const string sql = @"
            SELECT TOP 100 TimeStamp, RealValue
            FROM dbo.TagUncompressed
            WHERE ValueID = @vid
            ORDER BY TimeStamp DESC;";

        using (var cn = new SqlConnection(connectionString))
        using (var cmd = new SqlCommand(sql, cn))
        {
            cmd.Parameters.AddWithValue("@vid", valueId);
            cn.Open();
            using (var rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    DateTime utc   = rdr.GetDateTime(0);
                    DateTime local = UtcToLocal(utc);
                    Console.WriteLine($"{local:yyyy-MM-dd HH:mm:ss.fff}\t{rdr.GetDouble(1)}");
                }
            }
        }
    }
}

Reference: Microsoft Docs — TimeZoneInfo.ConvertTimeBySystemTimeZoneId.

WinCC Configuration Items That Affect Time Conversion

Verification Checklist

After applying the conversion, run these checks in order:

1. Current row check: SELECT MAX(TimeStamp) FROM dbo.TagUncompressed; against the same value displayed in an OnlineTrendControl within 1 second.
2. Boundary check: Pick a row stamped 2024-03-31 00:30:00 UTC (i.e., 01:30 CET, 02:30 CEST). Confirm your converted local time is 02:30 after the boundary and 01:30 before.
3. Historical span check: Plot a 1-year trend. Verify that no hour is skipped on the spring-forward day and no hour appears duplicated on the fall-back day. Duplicates in autumn confirm your query is reading raw UTC; missing rows on the spring day suggest you double-applied an offset.
4. Alarm coincidence check: Export the same alarm from the AlarmControl (which auto-converts) and from a manual SQL query against dbo.MSArcNew. Both timestamps must agree after your conversion.
5. DST regression test: Re-run step 1 at 01:30 local on the day of the next DST switch. The converted timestamp must jump by exactly +1 hour (or fall back by 1 hour) at the boundary, never continuously drift.

Troubleshooting Matrix

FAQ