Questions tagged [timezone]

A time zone is a region on Earth that has a uniform, legally mandated standard time. It is often represented by an identifier, such as "America/Los_Angeles". Note that a numeric modifier such as "-07:00" is an offset. A time zone encodes additional details like Daylight Saving Time.

A time zone is a region on Earth that has a uniform, legally mandated standard time. As legal definitions of zones can vary wildly and change often, a database or lookup table is often required to properly apply time zone rules.

Questions using this tag will usually relate to conversion issues between timezones or conversion bewteen datatypes where one has timezone data encoded and the other does not.

A helpful shareable reference for single timestamp comparisons is https://www.timeanddate.com/worldclock/converter.html

Please see the related tag wiki on StackOverflow for very long-form reading on the matter.

149 questions
46
votes
7 answers

How to handle TimeZone properly in SQL Server?

My local development server is in the Middle East, but my production server is in the UK. I need to show the date to the user in their time zone. For example, if a user is in Saudi Arabia then I need to show the time according to Saudi Arabia…
39
votes
2 answers

How to best store a timestamp in PostgreSQL?

I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps. Assumptions Users in different timezones will use the database for all CRUD functions. I have looked at 2 options: timestamp NOT NULL DEFAULT (now() AT TIME ZONE…
Bam
  • 579
  • 1
  • 5
  • 10
35
votes
11 answers

How can I get the correct offset between UTC and local times for a date that is before or after DST?

I currently use the following to get a local datetime from a UTC datetime: SET @offset = DateDiff(minute, GetUTCDate(), GetDate()) SET @localDateTime = DateAdd(minute, @offset, @utcDateTime) My problem is that if daylight savings time occurs…
Rachel
  • 8,547
  • 20
  • 51
  • 74
35
votes
2 answers

Convert Postgres TIMESTAMP to TIMESTAMPTZ

I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller. Stupidly, when I initially designed the thing I had all the fields as TIMESTAMP…
GTF
  • 453
  • 1
  • 4
  • 6
26
votes
3 answers

How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"?

When I run the following command I get an error, however one of my scripts requires it. SET time_zone = 'UTC'; ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
21
votes
1 answer

How to convert a unix time to PostgreSQL' Timstamp without timezome?

I have a PostgreSQL database running on a server which has its time zone set to India's Time Zone (i.e. UTC +5:30) I have some data in a table which is created like this: CREATE TABLE "CLOUDDATA" ( "CD_Tm_Obs" timestamp without time zone, …
Devdatta Tengshe
  • 631
  • 4
  • 9
  • 16
20
votes
2 answers

Why is AT TIME ZONE nondeterministic?

SQL Server 2016's AT TIME ZONE appears to be nondeterministic. However, I haven't been able to find documentation officially stating this or giving a rationale as to the reasoning behind it. Why is AT TIME ZONE nondeterministic? Example Showing…
Ben Gribaudo
  • 659
  • 4
  • 13
18
votes
1 answer

How do I generate a date series in PostgreSQL?

If you're looking to generate a time series, see this question Let's say that I want to generate a series of dates between two dates. I see the function generate_series provides only Function Argument Type …
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
16
votes
1 answer

Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres?

In Postgres 9.5, I was surprised to see the result seen below while experimenting with year 0001 (no year zero 0000). Offset of -07:52:58? Some example code. Note that I mixed use of TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE, so read…
Basil Bourque
  • 11,188
  • 20
  • 63
  • 96
14
votes
2 answers

Handling time zones in data mart/warehouse

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and in books), a common solution seems to be to have…
13
votes
3 answers

"AT TIME ZONE" with zone name PostgreSQL bug?

I was answering this stackoverflow question and found strange result: select * from pg_timezone_names where name = 'Europe/Berlin' ; name | abbrev | utc_offset | is_dst ---------------+--------+------------+-------- Europe/Berlin | CET…
sufleR
  • 678
  • 7
  • 18
12
votes
1 answer

How do I get the timezone of an SQL server?

How can I query the current timezone setting for an SQL server with respect to some offset like UTC?
pavanmanideep
  • 131
  • 1
  • 1
  • 3
11
votes
1 answer

Oddities with AT TIME ZONE and UTC offsets

I don't understand the difference between these two columns. America/Chicago timezone is UTC-6, so I expect both to return the same result: select timezone('America/Chicago', '2017-01-01 12:00:00'::TIMESTAMP AT TIME ZONE 'UTC'), …
Asaf David
  • 245
  • 2
  • 7
10
votes
2 answers

MySQL timestamp timezone handling

Does the timestamp data type represent an instant-in-time or a year-month-date-hour-minute-second value? An instant in time is a moment, like the moment you started reading this, and it can be described with many different y-m-d-h-m-s+timezone…
fejesjoco
  • 457
  • 1
  • 5
  • 14
10
votes
2 answers

Data Warehouse design for reporting against data for many time zones

We are trying to optimize a data warehouse design that will support reporting against data for many time zones. For example, we might have a report for a month's worth of activity (millions of rows) that needs to show activity grouped by the hour of…
1
2 3
9 10