Questions tagged [timestamp]
For questions about timestamp data types and/or recording a particular point in time.
304
questions
1
vote
0
answers
23
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 ...
- 176
1
vote
0
answers
17
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
1
vote
1
answer
35
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 ...
- 113
0
votes
1
answer
42
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 ...
- 281
-1
votes
1
answer
21
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?
- 225
2
votes
1
answer
109
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 ...
- 21
2
votes
1
answer
46
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 ...
- 226
1
vote
1
answer
51
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,
...
- 29
0
votes
0
answers
109
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 (...
0
votes
1
answer
359
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 ...
- 3
0
votes
1
answer
64
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 ...
- 29
0
votes
2
answers
59
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. ...
- 25
1
vote
2
answers
425
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 | ...
- 135
0
votes
1
answer
507
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 ...
- 37
0
votes
0
answers
177
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 ...
2
votes
1
answer
175
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, ...
- 145
1
vote
1
answer
3k
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 ...
0
votes
0
answers
132
views
Migrate timestamp from SQL Server to MySQL
I'm trying to replicate data from a MSSQL server into a MySQL server. To that end I'm using a linked server with ODBC driver 5.3.
I have a version column that is defined as timestamp both in the MySQL ...
- 101
0
votes
1
answer
310
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 ...
- 99
0
votes
1
answer
1k
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(*...
- 199
0
votes
1
answer
2k
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 ...
- 245
0
votes
1
answer
291
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 ...
- 476
0
votes
1
answer
2k
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 ...
- 441
2
votes
2
answers
5k
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 |
+--------------...
- 441
1
vote
1
answer
220
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 ...
user244938
1
vote
1
answer
1k
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
- 155
1
vote
1
answer
6k
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' ...
1
vote
1
answer
330
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 ...
- 17
0
votes
3
answers
135
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, ...
- 1
-2
votes
1
answer
1k
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 ...
- 11
0
votes
0
answers
222
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 ...
- 111
8
votes
2
answers
1k
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 ...
-1
votes
1
answer
995
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
...
1
vote
1
answer
777
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 ...
- 61
-1
votes
1
answer
58
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 ...
- 1
2
votes
1
answer
451
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 ...
- 121
5
votes
1
answer
489
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 ...
- 701
0
votes
1
answer
51
views
Date calculations give different results in different environments
This query:
WITH RECURSIVE
cte AS ( SELECT 0 n
UNION ALL
SELECT n + 1 FROM cte WHERE n < 20)
SELECT n, ('2020-04-13 00:00:00 Europe/Berlin'::timestamptz - make_interval(days => ...
- 537
2
votes
1
answer
569
views
Add a number of days to a timestamp
I'd like to add (or rather subtract) a number of days from a timestamp.
For example from 2020-04-13 00:00:00 Europe/Berlin I want to subtract 20 days and I expect the result to be 2020-03-24 00:00:00 ...
- 537
5
votes
1
answer
964
views
How does PostgreSQL store timestamp internally?
In case of MySQL,
TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC)`
In case of PostgreSQL with the version less than or equal to 9.6
timestamp values ...
- 153
3
votes
1
answer
1k
views
Is it possible to automatically generate a Unix millisecond timestamp in MySQL 5.7
I have created a created_time field with BIGINT in a MySQL 5.7 table, now I want to auto generate a Unix millisecond timestamp when I insert a record. Is it possible to do this? I have tried the code ...
- 631
0
votes
2
answers
7k
views
MySQL - Truncated incorrect datetime value
I need to migrate some data from between to tables. The first has a varchar(255) field where a date is stored. I want to insert that date in a timestamp field in the second table.
The varchar field ...
- 1
3
votes
1
answer
1k
views
Cross join table row with generate_series
Currently I have
SELECT x, plans.name
FROM plans
CROSS JOIN generate_series(timestamp '2021-01-01 06:00'
, timestamp '2021-01-07 22:00'
, interval '1 hour') ...
- 133
1
vote
1
answer
158
views
Getting the latest timestamp in Oracle
I am trying to create a report that has the latest member id in the first column and the old member id in the second column based on the timestamp in audits table below. Member ids are stored in ...
- 11
0
votes
3
answers
1k
views
Unix timestamp vs timestamptz index performance [closed]
I'm new to the world of DB admin and have a large table (2 billion rows) of cryptocurrency tick data for multiple currencies in a Postgres database. Most of my queries will involve selecting a moving ...
- 101
-1
votes
2
answers
415
views
How to create an index based on a time-based function in postgres? [closed]
I have a function called hot_rank to rank posts, based on their score, and their newest_comment_time (basically a decaying power function):
create or replace function hot_rank(
score numeric,
...
- 99
0
votes
3
answers
925
views
What is the difference between these extracting date method?
Assuming I have a table called MyTable, with a TimeStamp field "Created". I want only extract the Date part of this column, and in my research I found these alternatives:
SELECT created AS &...
- 147
-2
votes
2
answers
546
views
Purpose to use timestamp datatype in sql server? [closed]
What is the purpose to use timestamp datatype in sql server?
Do you have a concrete example of using timestamp in real business case?
When is it relevant to use timestampe datatype?
Thank you!
- 781
0
votes
1
answer
171
views
sql backup restore converts createdAt to local TZ
I have a remote MySQL DB in the cloud on JAWSDB.
I have createdAt and updatedAt timestamps for records that are in UTC TZ.
I'll backup these DBs using mysqldump -h HOST -u USER -p > backup.sql. I ...
- 211
0
votes
1
answer
858
views
Select count of timestamp formatted field in short date format to give number of rows
I have a field titled DTUpdated and it is a timestamp field.
I am trying to select a count of the dates in the rows that are xx old. i.e. 0-3 months, 3-6 months etc.
I am not at the writing date ...
- 3