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 'UTC') - bigint NOT NULL DEFAULT For **timestamp** I would send a string that would represent the exact (UTC) timestamp for the INSERT moment. For **bigint** I would store the exact same thing, but in a number format. (time zone issues are handled before millis is handed over to the server, so always millis in UTC.) One main advantage with storing a bigint could be that it would be easier to store and to retrieve, as passing a correctly formatted timestamp is more complex than a simple number (millis since Unix Epoc). My question is which one would allow for the most flexible design and what could be the pitfalls of each approach.

I run our company's DBMS in containers, and when creating a container, the ansible deployment role mounts the host file /etc/localtime along the same path into the container (although, as far as I understand, the behavior would be the same if the DBMS was running as a service on the host). In previous instances, the local time zone (other than UTC) was set in postgresql.conf, but now one of the development teams has decided to work with the UTC time zone in DBMS. I created a new test instance (version 15.3) for them and specified TimeZone = 'Etc/UTC' in the configuration, and an non-obvious problem arose, which cost me a considerable number of working hours before I finally figured out what was happening: Despite the fact that the query show timezone; showed the time zone Etc/UTC, in fact all requests related to the time output (for example select current_timestamp;) worked as if the time zone from /etc/localtime was really there. If I'm through SET TimeZone TO '…'; specified other time zone except Etc/UTC — the behavior became expected, this other time zone was applied. When returning to Etc/UTC, the time zone from /etc/localtime was returned again. Etc/UTC behaves as expected only if I did not mount the file from the host at all, in which case a file with the UTC zone from the container image is used. However, I think in this case most likely the behavior is not really different, it's just that here the time zone from the file coincided with the DBMS setting. Does the Etc/UTC time zone have any special meaning (something like «consider that the system time zone is used as UTC») or is this not a feature but a bug? What would be more correct for me to do: remove the /etc/localtime mount and use Etc/UTC or mount the file and change the time zone to, for example, GMT or +00? ## Addition Queries (executed in pgcli):

> show timezone;
+----------+
| TimeZone |
|----------|
| Etc/UTC  |
+----------+

> select * from pg_timezone_names limit 2000;
+----------------------------------------+--------+------------------+--------+
| name                                   | abbrev | utc_offset       | is_dst |
|----------------------------------------+--------+------------------+--------|
| …                                      |        |                  |        |
| Etc/GMT0                               | GMT    | 0:00:00          | False  |
| Etc/Greenwich                          | GMT    | 0:00:00          | False  |
| Etc/UCT                                | CEST   | 2:00:00          | True   |
| Etc/UTC                                | CEST   | 2:00:00          | True   |
| Etc/Universal                          | CEST   | 2:00:00          | True   |
| Etc/Zulu                               | CEST   | 2:00:00          | True   |
| …                                      |        |                  |        |
+----------------------------------------+--------+------------------+--------+

> select current_timestamp;
+-------------------------------+
| current_timestamp             |
|-------------------------------|
| 2024-04-04 06:18:05.994608+02 |
+-------------------------------+

> set TimeZone to 'Etc/GMT';
> select current_timestamp;
+-------------------------------+
| current_timestamp             |
|-------------------------------|
| 2024-04-04 04:21:06.581204+00 |
+-------------------------------+

How do I set a timestamp column whose default value is the current UTC time? MySQL uses UTC_TIMESTAMP() function for UTC timestamp: mysql> SELECT UTC_TIMESTAMP(); +---------------------+ | UTC_TIMESTAMP() | +---------------------+ | 2012-07-01 11:36:35 | +---------------------+ 1 row in set (0.00 sec) So I've tried: CREATE TABLE blah ( creation_time TIMESTAMP DEFAULT UTC_TIMESTAMP, ... And other variations, like UTC_TIMESTAMP(), but without success.

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'), timezone('UTC-6' , '2017-01-01 12:00:00'::TIMESTAMP AT TIME ZONE 'UTC'); However, the result is: 2017-01-01 06:00:00 | 2017-01-01 18:00:00 Also, this behavior is highly awkward, SELECT '1:00 -1'::time with time zone AT TIME ZONE '-1'; timezone ------------- 03:00:00+01 Can anyone explain this?

Why does Oracle think that the timezone offset of Denver is UTC-7:00? The server time and systimestamp look right. **Server 1: Oracle 11.2.0.4.0** SELECT SESSIONTIMEZONE FROM DUAL; America/Denver cat /etc/sysconfig/clock ZONE="America/Denver" SELECT * FROM v$timezone_file; timezlrg_14.dat 14 SELECT systimestamp FROM dual; 29-JUN-21 01.29.21.546036000 PM -06:00 SELECT tzabbrev, SUBSTR(tz_offset(tzname), 1, 6), tzname FROM v$timezone_names tz WHERE tzname = 'America/Denver'; LMT -06:00 America/Denver MST -06:00 America/Denver MDT -06:00 America/Denver MWT -06:00 America/Denver MPT -06:00 America/Denver **Server 2: Oracle 11.1.0.6.0** SELECT SESSIONTIMEZONE FROM DUAL; America/Denver cat /etc/sysconfig/clock ZONE="America/Denver" UTC=false ARC=false SELECT * FROM v$timezone_file; timezlrg.dat 4 SELECT tzabbrev, SUBSTR(tz_offset(tzname), 1, 6), tzname FROM v$timezone_names tz WHERE tzname = 'America/Denver'; LMT -07:00 America/Denver MST -07:00 America/Denver MWT -07:00 America/Denver MDT -07:00 America/Denver

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 between GetUTCDate() and @utcDateTime, the @localDateTime ends up being an hour off. Is there an easy way to convert from utc to local time for a date that is not the current date? I'm using SQL Server 2005

I use PostgreSQL 11 and have two columns. One is unix timestamp which is not aware of leap seconds and an utc timestamp which is aware of leap seconds. Is there a way either to find the number of leap seconds between an unix timestamp (without leap Seconds) and an utc timestamp (with leap seconds)? Alternatively is there a way to convert an utc timestamp (with leap seconds) into an unix timestamp (without leap seconds)?

UTC time is showing incorrectly in mysql 5.1. currently, it is 9 AM mysql> SELECT UTC_TIMESTAMP, UTC_TIMESTAMP( ); +---------------------+---------------------+ | UTC_TIMESTAMP | UTC_TIMESTAMP( ) | +---------------------+---------------------+ | 2018-08-01 14:15:03 | 2018-08-01 14:15:03 | +---------------------+---------------------+ 1 row in set (0.00 sec) SELECT current_timestamp dt1, utc_timestamp dt2, @@global.time_zone tz1, @@session.time_zone tz2; +---------------------+---------------------+--------+--------+ | dt1 | dt2 | tz1 | tz2 | +---------------------+---------------------+--------+--------+ | 2018-08-01 09:15:56 | 2018-08-01 14:15:56 | SYSTEM | SYSTEM | +---------------------+---------------------+--------+--------+ 1 row in set (0.01 sec) Please let me know how to fix it.

How should I convert historic local time to UTC taking into account of daylight saving? The main problem is the daylight saving doesn't occur at a fixed period every year and hence I cannot think of a way to do this task.

on SQL Server, i'm trying to query all of last months data based off of the time stamp, however, all those timestamps are in UTC and i need them to be in MST (the reason is mainly for the end dates) So far this is what I have: Select Vehicle.Timestamp WHERE Vehicle.Timestamp >= DATEADD(mm,DATEDIFF(mm,32,GETUTCDATE()),0) AND Vehicle.Timestamp < DATEADD(mm,DATEDIFF(mm,0,GETUTCDATE()),0) order by Vehicle.Timestamp i'm not entirely sure which way to move forward. I've seen the use of switchoffset however, not sure the best way to apply it.

If I want get Ubuntu system time, I have two options: $ cat /etc/timezone US/Eastern $ date Sun Sep 15 14:45:02 EDT 2013 How can I find out if PostgreSQL is using utc or a utc offset, such as US/Eastern?

Let's say I have a world wide website ( I mean it has uses from all around the world). I have 1 server which is in germany ( at some city). it will never be moved from germany - ever. My question is : When users saves data , I need to save the action date : Should I save ( and I think I do) it as UTC ? Or since - the server is in one location - it is ok to save all the dates as GetDATE() so the point of relativity is the same point. Am I wrong ? If I do , can you please supply a simple scenario where it's gonna fail ?