I need to review the current date and time zone from Elasticsearch. I'm checking the elastic documentation and it mentioned that the default value is UTC. In other environments I use:

SELECT NOW();

Is there any similar function for Elasticsearch?

I have a Hibernate with Oracle JDBC based application inserting/updating into the following column; COLUMN_A TIMESTAMP WITH TIME ZONE DEFAULT systimestamp NOT NULL but while it automatically appends timezone, it uses TZR, e.g. UTC, and weird thing with this data type in Oracle is that, it retains the format of timezone from insertion when we select it without any formatting, [explained here](https://stackoverflow.com/questions/51417100/timestamp-with-timezone-retains-timezone-format) With this said, we are utilizing Golden Gate that is replicating this data to MongoDB, but afaik it requires these timestamps to contain only TZH:TZM formatting, so I have this issue where one side blocked by Oracle JDBC, where insertion is done with TZR, and one side blocked by Golden Gate where data is expected with TZH:TZM Are there any way to handle this issue? Can I not forbid a certain formatting for TIMESTAMP WITH TIME ZONE ? Using NLS_TIMESTAMP_TZ_FORMAT works for SELECT formatting, but it is not usable for Golden Gate I was told. Also for INSERT case I was able to utilize default value of the column, and using systimestamp does insert with TZH:TZM, but for UPDATE case, I am unable to achieve this. Totally stuck! I have [this question](https://stackoverflow.com/questions/51412424/update-column-to-default-value-without-specifying-it-in-spring-data-jpa) with focus on application side of this issue. I am wondering if there is anything that can be done in DB side?

It seems that altering a column from timestamp without time zone to timestamp with time zone converts the existing data based on the current session time zone at the time of the alter statement. See this example, output shown after each statement

create table tztest (col1 timestamp without time zone);

set timezone = 'UTC';
insert into tztest (col1) values ('2023-02-01 10:10:10');
select col1 as t1, extract(epoch FROM col1) from tztest;
-- → 2023-02-01 10:10:10	1675246210

set timezone = 'America/New_York';
alter table tztest alter column col1 type timestamp with time zone;
select col1 as t2, extract(epoch FROM col1) from tztest;
-- → 2023-02-01 10:10:10-05	1675264210

set timezone = 'UTC';
select col1 as t3, extract(epoch FROM col1) from tztest;
-- → 2023-02-01 15:10:10+00	1675264210

The epoch value changes after the alter command. I was expecting that PG would assume the timestamp value was UTC and not adjust it when changing the type to timestamp with time zone (giving me t2 of 05:10:10 and t3 of 10:10:10). However, it seems PG assumes the session time zone at the time of the alter, and converts them to UTC. Is my understanding correct, and is it expected behavior? On a large table then the alter will update every row, which is something we are concerned about and certainly want to understand.

PostgreSQL 14.6
Ubuntu 22.04 I am using postgresql-42.5.4.jar which I downloaded from pgJDBC . I use this library to get data from a database and display it on a website running locally. The web server and database server are both running on the same machine. The database server's time zone is UTC. The system's time zone is America/Chicago. I have a table that contains a column of type timestamp with time zone. The data is inserted into the table by a separate C++ program that uses a completely different library. In order to insert the timestamps, it uses a Unix timestamp and the to_timestamp () function, like this: insert into my_table (my_time) values (to_timestamp (1654321098)); The timestamp is retrieved from the table as a string and passed back to the website as is. A comment below suggested using the java.sql.OffsetDateTime class but I don't know where that class would be used. Here is the Java code I am using: String query = "select my_time from my_table"; ResultSet result_set = db_connection.createStatement ().executeQuery (query); String result = result_set.getString ("my_time"); When I query this column from my command line database client, it shows me the dates in UTC, which is what I would expect because that is the time zone the server is using. This simple query would look like this: select my_time from my_table; While still in my command line client, if I want to display that column in my local time, I have to modify my query like this: select my_time at time zone 'America/Chicago' as my_time from my_table; But I started noticing that the website was displaying incorrect times. I temporarily had it print its query to the screen so I could look at it in my command line client. The result was not the same. In order to display the time in my local time on the website, I had to remove the at time zone 'America/Chicago' part of the query, which does not seem to make sense and does not produce the same result in the command line client, and it also makes the code less portable if I were to move it to a system using a different database library. Does the Java driver for PostgreSQL automatically convert timestamp fields to local time? If it does, is there a way to turn that feature off? If it doesn't, then what could be causing the different results I get between the JDBC library and my command line client?

I have a table and index in a PostgreSQL 10.18 database:

CREATE TABLE some_table (
    expires_at timestamptz
);
CREATE INDEX ON some_table(expires_at);

Is there a way to write this query in a way to use the index on expires_at?

SELECT
    *
FROM some_table
WHERE 
    TIMEZONE('America/New_York', expires_at)::date
  < TIMEZONE('America/New_York', NOW())::date
LIMIT 5;

America/New_York is added as an example, this query is run by using different time zones.

Until this year Turkey was using daylight saving time, staying UTC+2 on winter period, UTC+3 on summer. This year it is decided to stay only in UTC+3. Our DateTime's are persisted on MySQL (5.26.34) db (on AWS) was using UTC timezone. When i try to use CONVERT_TZ(DateTime, UTC, EUROPE/ISTANBUL) for recent datetime -lets say- 2016-11-21 00:00 it tries to convert according to UTC+2, whereas it suppose to be UTC+3. When i would tried to convert 2015-11-21 00:00 -last year- it suppose to convert according to UTC+2, since Turkey was in UTC+2 at that time. I Found an announcement on AWS forum, advicing to use Moscow timezone, which is not an option for us since Turkey's timezone situation is unique and our DateTime's are saved in the form of UTC0. https://forums.aws.amazon.com/ann.jspa?annID=4115

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 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 | 01:00:00 | f and next query select id, timestampwithtimezone, timestampwithtimezone at time zone 'Europe/Berlin' as berlin, timestampwithtimezone at time zone 'CET' as cet from data ; id | timestampwithtimezone | berlin | cet -----+------------------------+---------------------+--------------------- 205 | 2012-10-28 01:30:00+02 | 2012-10-28 01:30:00 | 2012-10-28 00:30:00 204 | 2012-10-28 02:00:00+02 | 2012-10-28 02:00:00 | 2012-10-28 01:00:00 203 | 2012-10-28 02:30:00+02 | 2012-10-28 02:30:00 | 2012-10-28 01:30:00 202 | 2012-10-28 02:59:59+02 | 2012-10-28 02:59:59 | 2012-10-28 01:59:59 106 | 2012-10-28 02:00:00+01 | 2012-10-28 02:00:00 | 2012-10-28 02:00:00 I'm using PostgreSQL 9.1.2 and ubuntu 12.04. Just checked that on 8.2.11 result is the same. According to documentation it doesn't matter if I use name or abbreviation. Is this a bug? Am I doing something wrong? Can someone explain this result? **EDIT** For the comment that CET is not Europe/Berlin. I'm just selecting values from pg_timezone_names. select * from pg_timezone_names where abbrev ='CEST'; name | abbrev | utc_offset | is_dst ------+--------+------------+-------- and select * from pg_timezone_names where abbrev ='CET'; name | abbrev | utc_offset | is_dst ---------------------+--------+------------+-------- Africa/Tunis | CET | 01:00:00 | f Africa/Algiers | CET | 01:00:00 | f Africa/Ceuta | CET | 01:00:00 | f CET | CET | 01:00:00 | f Atlantic/Jan_Mayen | CET | 01:00:00 | f Arctic/Longyearbyen | CET | 01:00:00 | f Poland | CET | 01:00:00 | f ..... During winter Europe/Berlin is +01. During summer it is +02. **EDIT2** In 2012-10-28 timezone has change from summer time to winter time at 2:00. This two records have the same value in Europe/Berlin: 204 | 2012-10-28 02:00:00+02 | 2012-10-28 02:00:00 | 2012-10-28 01:00:00 106 | 2012-10-28 02:00:00+01 | 2012-10-28 02:00:00 | 2012-10-28 02:00:00 This suggest that if I use one of abbreviations (CET or CEST) for big data range (summer time and winter time) result will be wrong for some of records. Will be good if I use 'Europe/Berlin'. I changed the system time to '2012-01-17' and pg_timezone_names has changed also. select * from pg_timezone_names where name ='Europe/Berlin'; name | abbrev | utc_offset | is_dst ---------------+--------+------------+-------- Europe/Berlin | CEST | 02:00:00 | t

I've been doing quite some research on different ways of 'seeing' time and how to properly map it in Postgres but I'm still not certain on what to actually use. Several articles recommend or rather persuade you to store dates as timestamp with time zone and never as just timestamp. I'm especially struggling with daylight saving times. My use case is a simple end-user-facing application accessed from only folks in the 'Europe/Berlin' timezone. Users write posts that get stored along with timestamps for creation and updates. Let's say a user publishes a post on 2020-01-01T10:00:00+01. If a user now reads that post on the **same day** it should display posted on 1st of January at 10 am. If that same article gets clicked in **Berlin in July** it should still say posted on 1st of January at 10 am regardless of the DST. My intuition is now to store the time as a timestamp without time zone because otherwise Postgres would convert that time to UTC and store it that way. Later I wouldn't be able to refer to the actual timezone that post was published in. In that case, it would read posted on 1st of January at 11 am (due to Berlin now beeing ahead two hours) which could confuse the author if they were to check the time they initially published the post. Are my thoughts correct and did I found one of the corner cases where to not use timestamp with time zone or am I missing something crucial here?

I have an SQLite table containing a "last changed" column with a date in the format e.g. "2022-11-07T11:51:06+01:00". Coreutils' date outputs this by using the following command: date +%FT%T%:z I can almost generate it from inside SQLite by using SELECT STRFTIME('%Y-%m-%dT%H:%M:%S', DATETIME('now', 'localtime')); however, this lacks the timezone, and as far as I grasp the docs, there's no timezone placeholder. So: Can I get the current local timezone using SQLite functions?

I'm using Postgres Debezium source connectors, which does not support columns with timezone . I want to convert a table column from type from timestamptz to timestamp. Postgres table stores data in UTC time zone only, ideally there should not be any value change from this conversion. What is the best way to achieve this?

I am doing some imports of data that includes time zone conversion. Data that I am importing is in America/New_York timezone and I have to import it as UTC timestamp. I tried some examples I found on the internet, like:

to_timestamp(tar.read_at_date || ' ' || tar.read_at_hour, 'MM/DD/YYYY HH24:MI')::timestamp without time zone at time zone 'America/New_York' at time zone 'utc'

and this seemed to work well for most of the data, however, I noticed some strange conversions at specific times, like Postgres is applying daylight saving for UTC time zone (???). To quickly illustrate behavior, look at examples and results below:

select ('2023-03-25 22:00:00'::timestamp at time zone 'America/New_York' at time zone 'utc');
result: 2023-03-26 03:00:00.000

select ('2023-03-25 23:00:00'::timestamp at time zone 'America/New_York' at time zone 'utc');
result: 2023-03-26 03:00:00.000

I am running these queries on:

PostgreSQL [15.5
PostgreSQL 15.5 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit]

Also, to mention, in 2023, daylight saving time in New York started on Sunday, March 12, 2:00 am. What is interesting here is that on March 26th 2023 at 2am is when clocks go forward in Europe. Any reasonable explanation why this happens?

A basic installation of MySQL creates, but does not populate, the several timezone-related tables in database "mysql". To populate those tables, you might consult: https://dev.mysql.com/doc/mysql-g11n-excerpt/8.0/en/time-zone-support.html (or similar for version 5.7). ... where the following command is shown

/usr/share/zoneinfo | mysql -u root -p mysql

However, this command fails. Solution in the answers below.

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 |
+-------------------------------+

Please see the code below: DECLARE @UKDateTime as DateTime SELECT @UKDateTime = GETDATE() AT TIME ZONE 'GMT Standard Time' print @UKDateTime PRINT GETDATE() This returns: Oct 4 2021 4:03PM Oct 4 2021 4:03PM The time in the UK is currently: 5:03pm (as confirmed by my PC clock). It appears to be returning the UTC date. What is the problem? I have SQL Server setup as a Docker container. It is SQL Server v18.4.

Let's start with an example. I've got an application that logs a point every minute. When I aggregate this data into a query where I'll have the count of logs per day, I get the following result. Keep in mind, that the query and the data is in UTC.

SELECT count(int_3) 
FROM data 
WHERE time >= '2023-10-26T00:00:00Z' 
AND time = '2023-10-26T00:00:00+02:00' 
AND time  The offset_interval is a duration literal. It shifts forward or back the InfluxDB database’s preset time boundaries. The offset_interval can be positive or negative.

For example let's use the first query (UTC) again and shift the start time and endtime one hour back.

SELECT count(int_3) FROM data WHERE time >= '2023-10-25T23:00:00Z' AND time = '2023-10-25T23:00:00Z' AND time = '2023-10-25T23:00:00+02:00' AND time < '2023-10-31T22:59:59+01:00' GROUP BY time(1d,23h) fill(none) TZ('Europe/Amsterdam')

2023-10-25T23:00:00+02:00 1260 2023-10-26T23:00:00+02:00 1440 2023-10-27T23:00:00+02:00 1440 2023-10-28T23:00:00+02:00 1440 2023-10-29T23:00:00+01:00 1320 2023-10-30T23:00:00+01:00 1440 ``` And there is the problem. 2023-10-29 should have one hour of point (60 points) more than the other days, but actually got 2 hours (120 points) less, which I find difficult to explain. Does somebody have an explanation or a solution for this? I'm using InfluxDB v1.8.10 and v1.11.1.

Similarly as in https://dba.stackexchange.com/questions/134385/convert-postgres-timestamp-to-timestamptz , but with the difference that I _don't_ plan to modify existing table's timestamp columns. **Are there any gotchas when using timestamptz for *new* tables, when all the existing ones use timestamp?** Presuming that the existing clients will continue to use the DB as thus far (by always converting timestamps to UTC on their end), is there anything else one should consider?

I 'm trying to use timezone setting with mariadb connector/J version 3.x. In the documentation,in Timezone consideration , it says: 'a timezone': connector will set connection variable to value. Compare to Auto, this avoids having some additional exchange with server at connection creation, *This value MUST correspond to java default timezone*. Can we set a specific timezone for a connection as with the previous setting **serverTimeZone** or can we only use system timezone? If it is possible to set it can someone provide an example of a connection string, because I tried it and it doesn't work.

I wanted to know if MySQL let the admin set a timezone per database ? I have three different databases for three different customers. Each of them being in their own timezone. I would like to load the exact same event for each customer and would like it to run every day at 00:00:01 for their respective timezone. Is it possible ? I can load three different SQL script specifying in each their respective timezone, but it doesn't scale well, and it make me have three versions of almost the same file.

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 WITHOUT TIME ZONE, and now I'm paying for it with annoying time-zone related bugs. I want everything to be explicit, so want to convert the field to TIMESTAMP WITH TIME ZONE. I realise that this doesn't store extra information, and all my timestamps are already in UTC, so the migration *should* be trivial, but I was wondering if there are any things complications / potential tripping blocks that will prove problematic (this is a production database with customers relying on it)?