513 questions
2
votes
1
answer
82
views
Editing "parts" of a TIMESTAMPTZ value
I have a TIMESTAMPTZ column in a Postgresql database. To my understanding, Postgresql does not preserve the timezone within a TIMESTAMPTZ column, and instead converts the given timestamp to an ...
- 55
2
votes
1
answer
108
views
Why is timestamptz + interval considered mutable?
I tried to create a generated column that adds timestamptz and interval columns, but Postgres complains that this expression is immutable:
create table test ( ...
- 8,881
-2
votes
2
answers
58
views
why doesn't timestamptz work in postgres?
I am keeping date_created in TIMESTAMPTZ
date_created TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
in java
@CreatedDate
@Column(nullable = false, updatable = false)
private OffsetDateTime ...
- 9
2
votes
2
answers
83
views
Why is string literal '2021-02-16 09:00' without timezone information interpreted as `timestamp with time zone` with AT TIME ZONE?
set timezone = 'Asia/Ho_Chi_Minh';
I set times displayed to GMT+7.
select '2021-02-16 09:00' AT TIME ZONE 'Asia/Singapore';
select '2021-02-16 09:00+06' AT TIME ZONE 'Asia/Singapore';
Output
2021-02-...
- 517
0
votes
0
answers
367
views
Hibernate 6 is storing Instant in UTC instead of EST with timestamp without timezone
I recently upgraded my Spring Boot application from Hibernate 5 to Hibernate 6 and encountered an issue where timestamps are now being stored in UTC in my PostgreSQL database, despite my JVM and ...
0
votes
0
answers
46
views
timestamps written by java.sql.SQLData are shifted by timezone specific number of hours
Can someone advice, why writing data using java.sql.SQLData shifts timestamps by N hours, where N is timezone offset?
Background: I have app using hibernate 6 and springboot 3, I need to call stored ...
- 3,267
0
votes
2
answers
1k
views
Oracle timestamp with timezone TZR/TZD handling
I'm trying to parse some strings into Oracle's TIMESTAMP WITH TIMEZONE with to_timestamp_tz.
The documentation suggests I should use TZD to parse three-letter codes like PST & PDT. However, that ...
- 4,109
0
votes
1
answer
6k
views
How can I 'tell' PostgreSQL what the timestamp format is when I INSERT a text string that represents a timestamp into a table?
NOTE: There are a few other questions that are very similar to this and which deal with the same kind of issues, but I really felt they did not articulate it well enough for me. So I am asking this ...
- 1,816
1
vote
1
answer
714
views
Is this SET TIME ZONE supposed to work like this with sqlx?
As explained here I'm using the below code:
use sqlx::Executor;
use sqlx::postgres::PgPoolOptions;
let pool = PgPoolOptions::new()
.after_connect(|conn, _meta| Box::pin(async move {
conn....
- 4,273
-1
votes
1
answer
96
views
from_unixtime still displaying 19 hours past expected time
I currently have some cleaned data with new columns and formatted correctly, however the clause regarding from_unixtime is stumping me. I have tried the following:
from_unixtime(`time@timestamp`, '%H:%...
- 1
0
votes
1
answer
1k
views
Convert normal human readable datetime to unix timestamp in Snowflake
I have created an SQL model in snowflake that gives a several outputs among those ones, I have two columns that are the next :
The first column: called last_used that gives a human readable datetime ...
- 417
0
votes
1
answer
172
views
Need to convert date format to Chicago tIME ZONE format as In azure data flow explorer expression builder , tried so many bit not useful , example is
I need to convert to csv date column in this format, tried so many in AZURE DATA FACTORY data flow expression builder but nothing works, can someone please help on this. Here for the last 4 digits , ...
0
votes
0
answers
41
views
POSIXlt object showing NA values after conversion
I am trying to add ride length calculation to all_trips data frame in seconds. To do that I have to use difftime() function and also convert start_time and end_time colunms to POSIXlt objects before ...
0
votes
0
answers
322
views
Timestamp comparison in spark sql
I have below query in spark sql
sparkSession.sql(f"( SELECT * FROM {ordersTable} \
WHERE lastUpdated >= current_timestamp() - INTERVAL 24 hours)")
The date-time in ...
- 2,834
0
votes
1
answer
37
views
How to convert the hour difference in timezone info to the abbreviated timezone name [duplicate]
This is a followup question to
How can I convert the time stamp with timezone info to utc time in python
I was able to get the time string like this "2023-09-06T22:02:44-07:00" through ...
- 417
0
votes
1
answer
1k
views
how to handle Invalid time zone with BigQuery
I'm working with a BigQuery table where I got date ingested as string originally generated by the Javascript method Date();. This is what my field looks like:
Sat Aug 05 2023 15:15:04 GMT-0700 (PDT)
...
- 2,904
0
votes
0
answers
60
views
Why does timestamp value changes between different presto versions?
I am running queries using presto jdbc jars. There is a time difference noticed for the same query when run against presto-jdbc-0.238.jar and presto-jdbc-0.82.jar.
The simple query which shows this ...
0
votes
2
answers
2k
views
Postgres convert timestamp with timezone to date
I have a postgres database, that stores some accounting data.
The accounting data have a timestamp (with timezone).
I would like to aggregate/group the accounting data by date.
But the date should be ...
- 2,727
0
votes
1
answer
35
views
Same location with different time in the year PostgreSQL
I got an issue with timestamptz (timestamp with timezone). I have New York time, ex: 2pm NY EST. I will input it as 14:00:00 - 04:00 (UTC-4) because it is July now. However, when it starts from ...
- 1
1
vote
0
answers
99
views
Apache Spark not reading UTC timestamp from MongoDB correctly
I've data stored in MongoDB collection and the timestamp column is not being read by Apache Spark correctly.
I'm running Apache Spark on GCP Dataproc.
Here is sample data :
In Mongo :
timeslot_date :...
- 1,101
0
votes
1
answer
54
views
PostgreSQL `at timezone` unexpected behaviour
When running the following queries, the expected result would be the same (2023-01-16 16:39:24.824000) as the current timezone in Europe/Tirana is CEST. However, the results I receive are as shown:
...
- 3
0
votes
1
answer
616
views
Timestamp value getting modified on receving the response
I'm receiving a Timestamp via API in a Timestamp(java.sql) object and after some other validations I'm sending response as a JSON containing that same timestamp value, but the format in which I sent ...
- 41
1
vote
1
answer
319
views
How to autogenerate insert timestamp with millisecond precision in UTC format in SQLite
I have the following table
CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY, insertTimestamp <- )
For the following column insertTimestamp I want to generate timestamp in this format: YYYY-...
- 185
3
votes
1
answer
1k
views
Get most recent streak (consecutive days) for given event and time zone
This is a tough one I've been fighting with for a bit. I have a table called action_events that stores event_name and timestamp:
CREATE TABLE action_events (
id SERIAL PRIMARY KEY,
timestamp ...
- 1,656
0
votes
1
answer
954
views
.Net Core 7 with Postgres, Cannot update record with timestamp with time zone as primary key
I have the following situation:
A postgresql 15 database with a table with a `timestamp with time zone' as part of the primary key.
A C# .NET Core 7 application using:
Microsoft.EntityFrameworkCore (...
- 113