All Questions
Tagged with timestamp postgresql
146 questions
5
votes
2
answers
615
views
How to retrieve data based on year to date in Postgres?
I'd like to retrieve data from "year to date" in my below query. I was hoping for something simple in the 'INTERVAL' but could not find anything.
I have the following:
SELECT
"...
- 65
1
vote
1
answer
166
views
Alter timestamptz column to timestamp, without re-writing the table
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 ...
- 113
0
votes
1
answer
32
views
Detect concurent objects based on category and timespan
I have a Postgres table with categorys and timespans (columns valid_from and valid_to).
This represents road sign that are installed for certain time span.
I want to compute how many road signs there ...
- 101
0
votes
1
answer
194
views
Postgres unexpected results with time zone conversions
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 ...
- 3
0
votes
1
answer
1k
views
Using dbeaver and postgresql can I declare a variable INTERVAL to pass into a sproc?
I have a PostgresSQL table that has a field for TIMESTAMP, and an associated stored procedure that takes an INTERVAL and calculates the difference from now() + in_interval. I want to use dbeaver to ...
- 103
0
votes
1
answer
46
views
Cannot make sense of timestamptz offset
Sample code:
show timezone;
TimeZone
---------------
Europe/Berlin
(1 row)
create table foo (ts timestamp without time zone);
insert into foo values ('2023-10-28 23:58:00');
insert into foo ...
- 105
1
vote
2
answers
957
views
Can I get the created time of a PostgreSQL DB?
I'm using PostgreSQL-13.0.
Is there a way I can get the created time or last modified time of a database?
Thanks!
- 413
4
votes
1
answer
324
views
Postgres strange conversion of the string 'now' to timestamp
It looks like postgres treats the string 'now()' the same as a call to the now() function.
Why does postgres allow this?
select 'now'::timestamp;
or this?
select 'now()'::timestamp;
or even this?
...
- 143
-1
votes
2
answers
438
views
Getting dates normalized to current year
I have a time series in a postgresql / timescale db server and I would like to plot it in grafana so I can easily compare different years.
At the moment I have two different queries in grafana for ...
- 261
1
vote
0
answers
105
views
Is there a way to set up custom global output formatting for timestamptz values?
In order to reduce friction between frontend/backend/database in regards of dates, I've decided to use a subset of RFC3339 which is also compatible with ISO 8601 and HTML. As per this graph that means ...
- 288
1
vote
0
answers
28
views
How to create a count for Each entry in Postgres Database?
So I have my Table that looks some thing like
Name
timestamp
abc
2022-03-08 06:15:11
bcd
2022-03-08 08:15:11
cvd
2022-04-10 06:15:11
Now each Name is unique in this table and it has a timestamp ...
- 11
0
votes
2
answers
2k
views
Java PostgreSQL library seems to convert dates to local time zone automatically
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 ...
- 283
3
votes
1
answer
493
views
PostgreSQL time series table with composite partitions
I am investigating how I might structure a PostgreSQL table to store a large amount of time stamped data that also needs to be portioned by another field.
My expected data structure will be something ...
- 45
2
votes
1
answer
1k
views
How are time value comparisons between `timestamp` and `timestamptz` carried out in postgres?
Given the following data:
CREATE TABLE tbl (
x timestamp,
y timestamptz
)
INSERT INTO tbl(x, y)
VALUES
('2021-01-01'::timestamp, '2021-01-01'::timestamptz)
;
It's not obvious to me that ...
- 326
0
votes
0
answers
453
views
Table design for Time series data
We have a system that does some calculations for a network consisting of nodes, edges and endpoints. This data is stored in a Postgresql DB. Each of these tables have entries for different attributes (...
