Is there a way to preserve locale when format a datetime in SQL?

Question

I have a table with objects that defines some endpoint to test latency on (structure), a table to hold the results (latencies), and a table of each "target" (place where the endpoint is tested)

I want to get the average latency for each time interval for a specific structure from all locations that it is being hit from

The timestamps are in 10 minutes intervals (differing by ~1 second) for each target under that structure

I'm having issues with grouping by the timestamp where formatting the timestamp (to remove the second difference) and casting it back to a datetime loses it locale (stored in UTC but appears to come out in local time)

Is there some better way to go about this or some way to format a date s.t. the locale is preserved?

SELECT 
    structure.Id as TargetId,
    Avg(latencies.Latency) as Latency,
    latencies.Timestamp
    FROM  StructureTable structure
    INNER JOIN TargetsTable targets on targets.StructureId = structure.Id
    Outer apply
    (
        Select
            CAST(format(Timestamp, 'yyyy-MM-dd hh:mm:00') as DATETIME) as Timestamp,
            Latency,
            TargetId
        from LatenicesTable
        where TargetId = targets.Id and Timestamp < endDate and Timestamp > startDate
    ) as latencies
    where structure.Id = structureId and latencies.Latency > 0
    group by latencies.Timestamp
    order by latencies.Timestamp asc

EDIT: results example w/ format + Cast

Id  Latency Timestamp
22  546     2021-05-09 01:00:00.000
22  540     2021-05-09 01:10:00.000
22  535     2021-05-09 01:20:00.000
22  543     2021-05-09 01:30:00.000
22  551     2021-05-09 01:40:00.000
22  546     2021-05-09 01:50:00.000

and w/o

Id  Latency Timestamp
22  548     2021-05-09 05:00:02.313
22  471     2021-05-09 05:00:02.453
22  619     2021-05-09 05:00:02.547
22  607     2021-05-09 05:00:02.593
22  477     2021-05-09 05:00:02.937
22  561     2021-05-09 05:10:01.627
22  641     2021-05-09 05:10:01.657
22  470     2021-05-09 05:10:01.673
22  481     2021-05-09 05:10:01.893
22  617     2021-05-09 05:10:02.000

If `timestamp` is `datetimoffset`, then format it [as `datetimeoffset`](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-ver15#supported-string-literal-formats-for-datetimeoffset) and not as `datetime` in the first place? Assuming this is SQL Server [to begin with](https://meta.stackoverflow.com/q/388759/11683). — GSerg, Jun 22 '21 at 17:06
It is sql-server yes, and it's being saved as a datetime - I'm not sure if the format function does some locale cast automatically or what but I added some example results above — tcdevens, Jun 22 '21 at 17:16
So if those are `datetime` in the first place, how do you know that they lose their locale? What do you mean by locale? Datetime does not have that information, and formatting functions do not try to change it because there is nothing to change. — GSerg, Jun 22 '21 at 17:21
One obvious flaw is that you are using [`hh:mm`](https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings#lowercase-hour-h-format-specifier) as `HH:mm` which will offset your times by 12 hours, but not by 4 like in your sample data. — GSerg, Jun 22 '21 at 17:30
I know all the stored timestamps are in UTC but I see what you're saying that they don't have associated timezone info. Turns out changing hh -> HH was the kicker - I'm seeing correct results using that (Not sure why it was throwing the time off by 4 hours specifically though) — tcdevens, Jun 22 '21 at 18:08
Does this answer your question? [A way to extract from a DateTime value data without seconds](https://stackoverflow.com/questions/8896663/a-way-to-extract-from-a-datetime-value-data-without-seconds) Specifically this answer seems best https://stackoverflow.com/a/8897059/14868997 — Charlieface, Jun 22 '21 at 21:18

Is there a way to preserve locale when format a datetime in SQL?

0 Answers0