Questions tagged [timestamp]

For questions about timestamp data types and/or recording a particular point in time.

Filter by
Sorted by
Tagged with
-2 votes
0 answers
13 views

Postgres - Add business days to timestamp

I'm trying to create an SLA date by adding 2 business days to a timestamp, how do a achieve this in postgres? I saw one post talking about date types but the solution did not work with timestamps ...
Mankind1023's user avatar
0 votes
0 answers
19 views

Timeseries data. Should we include both columns in the index if we have multiple timestamp columns(started_on, ended_on)? [closed]

Table: car_charging_location (id,vehicle_number, location_id, started_on, ended_on) Should we include both columns in the index if we have multiple timestamp columns(location_id, started_on, ended_on)?...
Rakesh Gupta's user avatar
0 votes
0 answers
16 views

Problems with minutes in timestamps when inserting data into InfluxDB via PowerShell

I am working with InfluxDB and trying to insert time series data via PowerShell. My goal is to insert data points with specific timestamps containing hours, minutes and seconds into InfluxDB. I am ...
michi's user avatar
  • 101
0 votes
1 answer
27 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 ...
persson's user avatar
  • 105
0 votes
1 answer
80 views

PostgreSQL date index

I have created a user table with 10M rows (fake data generated by Python) in PostgreSQL 16. The table columns are: email (varchar 100), password(bytea from sha512), created_at(timestamp without ...
OM222O's user avatar
  • 109
1 vote
2 answers
46 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!
Leon's user avatar
  • 337
4 votes
1 answer
278 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? ...
Christian Long's user avatar
0 votes
0 answers
34 views

Why a postgreSQL timestamp with time zone at a certain time zone is printed without the time zone

When I ask PostgreSQL for a timestamp with time zone at a certain time zone, it does not print the time zone: SELECT now()::timestamp WITH TIME ZONE AT TIME ZONE 'CST'; timezone --...
s.k's user avatar
  • 342
0 votes
0 answers
17 views

sp_get_query_template doesn't work with ODBC timestamp

I have the following query: SELECT COUNT(*) FROM (SELECT DISTINCT USER_NAME FROM LICENSE WHERE RESTRICTED_USE = 'T' AND ALLOCATED = 'T' UNION ALL SELECT USER_NAME FROM LICENSE WHERE RESTRICTED_USE = '...
Fausto Galati's user avatar
0 votes
1 answer
185 views

how to convert a timestamp value stored in varchar column to proper format and increase/decrease to value of year

In a PostgreSQL table, I have a column named insert_date of type varchar(114) with values stored as below: 2023-04-08 01:42:12.197779000 +01:00 Now I would like to add or subtract a number of years ...
Hnarang's user avatar
0 votes
1 answer
324 views

In Snowflake, how do I include a timestamp in a result that has zero rows?

I have run a query in Snowflake that returns zero rows. How do I add a timestamp to a query that returns zero rows?
Jimbo's user avatar
  • 65
3 votes
1 answer
675 views

In Snowflake, how do I CAST CURRENT_TIMESTAMP up to the minute only?

In Snowflake, how do I CAST CURRENT_TIMESTAMP up to the minute only. I don't want seconds, milliseconds, or timezone included in my result.
Jimbo's user avatar
  • 65
-1 votes
2 answers
81 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 ...
MortenSickel's user avatar
0 votes
0 answers
115 views

InfluxDB InfluxQL v1.8 : Wrong timestamps used for writing to database

I wrote a bash script for inserting the summary of the daily energy database "xxx_d" into the monthly database "xxx_m" - triggered on every 1st day of the month at 02 o'clock by a ...
6a5h4's user avatar
  • 1
1 vote
0 answers
32 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 ...
Biller Builder's user avatar
1 vote
0 answers
19 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 ...
tushar_ecmc's user avatar
2 votes
1 answer
2k views

Is it acceptable to store global date times in UTC without offset for timezone?

I'm looking at a Data Warehouse which collections data from systems across the world. The Data Warehouse uses UTC for time but only stores the UTC offset and not the timezone. I have noticed that ...
user3165854's user avatar
0 votes
1 answer
854 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 ...
Zephyrus's user avatar
  • 301
0 votes
1 answer
104 views

Why isn't it trivial to create a virtual integer timestamp column from a TIMESTAMP column in MySQL?

There's no way to extract an integer timestamp from a TIMESTAMP column in a virtual column context in MySQL because UNIX_TIMESTAMP is not allowed in virtual columns. Why isn't this a trivial task?
Dan's user avatar
  • 235
3 votes
1 answer
310 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 ...
chrestomanci's user avatar
2 votes
1 answer
614 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 ...
baxx's user avatar
  • 296
1 vote
1 answer
92 views

How to create a variable while using GROUP BY in MySQL

I have these functional queries where I need to change timestamp in UTC to timestamp with different time zones SELECT max(user_id) AS user_id, AVG((model_v3)*40) AS avg_score, ...
Rollo's user avatar
  • 29
0 votes
0 answers
388 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 (...
sebastian.do's user avatar
0 votes
1 answer
2k views

How to subtract from the current timestamp the number of hours stored in a column?

I am trying to retrieve data from a column with integer values by getting the difference between the current_timestamp and the values in the column. This works in DB2 by simply stating the value as ...
kunleoju's user avatar
0 votes
1 answer
228 views

How to group by week/month/year and time frame 8hr/11hr/14hr/17hr/20hr in MySQL

I need to group 2 tables (Key = analysis_id <=> id) where my output should show the last week or month or year, grouped by a Time range. I have around 4000 inputs from users. Example of tables ...
Rollo's user avatar
  • 29
0 votes
2 answers
104 views

Limit my ticker count to maximum 12 Months

I'm trying to write a case expression where it looks at the due date and if it has passed chooses the upcoming or closest due date. Now due to certain database restriction I have to do this manually. ...
NasahT's user avatar
  • 25
2 votes
2 answers
1k views

Postgres: why does to_timestamp return the date with different timezones, in the same query?

Why does to_timestamp use different timezones to display epochs, in the same query? Note +02 vs +01. => select to_timestamp(1651157248),to_timestamp(1647224832); to_timestamp | ...
gernot's user avatar
  • 145
0 votes
1 answer
690 views

Inserting a "date-time" into TIMESTAMP column -- "Incorrect datetime value"

I have a date-time data that comes in this format: 2016-02-10T12:00:00.000Z I've created a MariaDb database and a column for that date of type TIMESTAMP However, when inserting the data into the ...
Kum's user avatar
  • 47
0 votes
0 answers
313 views

TimescaleDb : How to store long time events?

I’m trying to use TimescaleDb to store long time events (events started for example 2 days ago and ended 1 hour ago). There are two types of events (current events without end date and historical ...
Anthony Le Gal's user avatar
2 votes
1 answer
328 views

Is there a way to use time series databases effectively without absolute timestamps?

I have multiple sets of scientific data (European Data File files for EEG data) that I am reading in via MNE Python. Each EDF can be read in with a Numpy array that contains the time basis, say 0.00, ...
jonsca's user avatar
  • 155
2 votes
1 answer
9k views

Recommended way to index a timestamp column in postgres?

I have a PostgreSQL table called tickets_details, which has many columns and is constantly updated only on the rows of the current day, it also inserts thousands of rows of the current day that have ...
Cristofer Feliz's user avatar
0 votes
1 answer
521 views

Why does PostgreSQL keep resetting my date to the wrong "timezone", with "seconds", as I try to remove it?

I asked this question at first: https://stackoverflow.com/questions/71979003/why-does-strtotime-get-confused-about-timezones-instead-of-ignoring-them-an I have this timestamp with timezone column ...
Homer's user avatar
  • 99
1 vote
1 answer
3k views

Time zone lost when returning timestamp with time zone from a function

I have this function: CREATE OR REPLACE FUNCTION laborable_day(dtDateTime TIMESTAMP WITH TIME ZONE) RETURNS TIMESTAMP WITH TIME ZONE AS $$ DECLARE _isHoliday BOOLEAN; BEGIN LOOP SELECT COUNT(*...
Ουιλιαμ Αρκευα's user avatar
2 votes
1 answer
4k views

Why do I see the timezone offset displayed in PgAdmin, for a timestamp with timezone

I'm using PgAdmin 4 v6, and for some timestamp data with data type of timestamp with time zone, I'm seeing it in PgAdmin like this: 2022-03-06 16:11:33+04 Why is the +04 being displayed? I've looked ...
Chris Halcrow's user avatar
0 votes
1 answer
651 views

Goldengate timestamp (FFF)

Working with Goldengate 19.1.0.0.211019 replicat inbound to Oracle 19.0.0.0.0 consuming a trail file incoming from Nonstop (ie a non-Oracle database). Column in question from Nonstop is a string ...
Ditto's user avatar
  • 506
0 votes
1 answer
4k views

MySQL 8.0 timestamp CURRENT_TIMESTAMP sets date to '0000-00-00 00:00:00' (was ok in 5.7)

The table T has this field +---------------+--------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra ...
Déjà vu's user avatar
  • 473
2 votes
2 answers
10k views

Does the timestamp type have (hidden) milliseconds?

The updated column was created as | updated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | a simple SELECT gives +---------------------+ | updated | +--------------...
Déjà vu's user avatar
  • 473
1 vote
1 answer
441 views

Storing timezoned HH:MM in PostgreSQL

My application needs to send a daily email to users at a time of their choosing, the timezone is also defined by the user itself. What's the best way to store HH:MM + timezone? And how can I ...
user avatar
1 vote
1 answer
2k views

How to GROUP by month & year with an epoch timestamp in AWS Redshift

Assuming a very simple schema as pictured below. (unfortunately the column is actually named timestamp) How do I simply get a count on records grouped by YEAR & MONTH
Andrew Wei's user avatar
1 vote
1 answer
12k views

Time zone conversion in PostgreSQL without timezone does the opposite operation

Query: SELECT TO_TIMESTAMP('2021-10-24 20:00', 'YYYY-MM-DD hh24:mi') as d, (TO_TIMESTAMP('2021-10-24 20:00', 'YYYY-MM-DD hh24:mi') at time zone 'utc')::timestamptz at time zone 'ASIA/CALCUTTA' ...
Prithvi Singh's user avatar
1 vote
1 answer
952 views

Convert Varchar to Interval

I have a Postgresql column that is a varchar that is saving a string that looks like '2 years, 5 months' or '3 months' or '9 years, 0 months'. I can't convert the column to an interval because it's ...
thesofaking's user avatar
0 votes
3 answers
191 views

Statement of total precipitation for each hour, day or month

I store the data from the rain gauge in mySQL and needed help with listing the data. I save the data every 5 minutes and use the save date in the form of a timestamp. At the end of each hour, ...
Novosad's user avatar
-2 votes
1 answer
2k views

query to get start time and end time from same column based on boolean value order by primary key [closed]

As of now, this is my current query: SELECT MAX(CASE WHEN status = 1 THEN [timestamp] END) as machineOn ,MAX(CASE WHEN status = 0 THEN [timestamp] END) as machineOff FROM machine_table where ...
woo25's user avatar
  • 11
0 votes
0 answers
367 views

How can I get all rows whos timestamp is closest to midnight (sampling timeseries)

I have a table that counts the amount of user interaction with a given item. Basically it has a table with 3 rows (item_name, time, num_interactions). The number of interactions never decreases, it ...
user2741831's user avatar
9 votes
2 answers
2k views

Is there a performance or storage space advantage to lowering time/timestamp precision in PostgreSQL?

PostgreSQL allows time/timestamp to specify a precision: time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds ...
Fabián Heredia Montiel's user avatar
-1 votes
1 answer
1k views

PostgreSQL Query - how to subtract timestamps and format durations (with builitin functions or otherweise)

I am looking for a solution to a problem with a select statement where I am trying to display the sleep duration. Sleep Table Here is the select statement I have so far: select wakeuptime,hour from ...
Badrinarayanan's user avatar
1 vote
1 answer
1k views

Why can't I use ALTER to set the default on a column?

Long story short, I have a table with a TIMESTAMP column where I want to make the default CURRENT_TIMESTAMP (default currently is 1971-01-01 00:00:00). This is part of a WordPress plugin, and so ...
nosilver4u's user avatar
-1 votes
1 answer
79 views

How to catagorize timestamp based on hours in postgreSQL [closed]

I am trying to categorize the timestamp by the HOUR using the arrival column categories like ['00.00 - 06.00', '06.00 - 10.00', '10.00 - 14.00', '14.00 - 18.00', '18.00 - 00.00']). I am very new to ...
Vicky's user avatar
  • 1
3 votes
1 answer
913 views

What is the reasoning behind PostgreSQL changing the timezone when changing a timestamp with time zone field?

I have a table with a "timestamp with time zone" column. For one row, it had this value: 2021-06-24 00:00:00+02. Then, I edited and saved it to be: 2021-06-24 11:00:00-05. Note that both the ...
Rockerduck's user avatar
5 votes
1 answer
900 views

Force NOW to return new timestamps without committing the transaction

I'm aware that, in PostgreSQL, the return value of NOW() is the transaction begin timestamp. So if you use NOW() multiple times in the same transaction it will always return the same value. And that's ...
exhuma's user avatar
  • 741

1
2 3 4 5
7