Questions tagged [timestamp]
For questions about timestamp data types and/or recording a particular point in time.
247
questions
0
votes
1answer
41 views
Postgres epoch from current_timestamp
I've learned that epoch (UNIX timestamp) is a unique independent amount, so I am baffled by output I get from the following queries:
select extract( epoch from current_timestamp::timestamp with time ...
0
votes
1answer
14 views
Querying the most recent records with two fields: datestamp and timestamp
I have a table called airquality that has records for air quality measurements taken on different days and times within those days. These date/timestamps are stored in two different columns: validdate ...
0
votes
1answer
26 views
Timestamp with no time zone - if I'm ok with UTC at all times, is there any downside to using it?
Timestamp with time zone vs timestamp with no time zone.
I'm aware that timestamptz is the same as timestamp no time-zone + (time-zone difference) that gets added upon saving a value in a database. ...
0
votes
3answers
28 views
Convert two integers into timestamp
How do I convert two integers, one for date and one for time, into a timestamp?
Given 20200304 and 1046, return a timestamp for April 3, 2020, 10:46 AM.
Given 20200304 and 946, return a timestamp for ...
0
votes
0answers
18 views
Syntax error around converting timestamps to time
I am writing a function that requires some comparison between the difference date and time parts of the timestamp data type. However it keeps running into syntax errors and I cannot find out what is ...
0
votes
1answer
24 views
Migrating timezone timestamps
I am trying to migrate this node library https://github.com/LocalSEOGuide/lighthouse-reporter from Postgres to MySQL and getting an error error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '...
0
votes
1answer
34 views
How can I output a legible timestamp from “1583981745.335346” (stored in a 'double' field type) via a select statement?
I have a table within a WordPress installation that stores timestamps in a format that I do not recognize, nor can I figure out how to translate it. In the MySQL database, it appears as "1583981745....
0
votes
1answer
24 views
Can I refuse inserts/updates with timestamps without time-zone in postgresql?
I use exclusively timestamps with time-zone in PostgreSQL, but I just noticed something slightly worrying: If a client inserts/updates a record with a naïve time-stamp, PostgreSQL falls back to a ...
1
vote
1answer
187 views
mongodump command with timestamp query
When I execute the below query in mongodb shell, it gives me the number of records updated after the mentioned timestamp.
db.getCollection('oplog.rs').find({"ts":{"$gt":Timestamp(1582823397,2)}})
I ...
0
votes
0answers
26 views
Storing Large Quantities of Time Series
What is the recommended (type) of database design for storing large amounts of time-series data?
The dataset:
2 million time-series;
Each of the time-series contains around 500 timestamps;
At ...
1
vote
1answer
26 views
Update record with converted epoch timestamp from another column
I'm trying to convert epoch data in one column (timestamp_epoch) to a legitimate timestamp in another column (timestamp) for every record in a table. In reading the PostgreSQL Documentation it ...
2
votes
1answer
45 views
Preserving timezones explicitly in postgres
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 ...
0
votes
0answers
30 views
fixing broken convert_tz() in Mysql?
I come across the documentation for convert_tz() in MySQL and it says
If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs. ...
3
votes
1answer
80 views
Why mysql throws “invalid default value for field” only when two or more timestamp fields exist in the create statement?
In mysql 5.7.28 I create a table like this:
create table t1 (
id int not null,
d1 timestamp)
engine=innodb;
which works fine and creates d1 as non-null with default value current_timetamp,on update ...
0
votes
2answers
234 views
Add timestamp to field automatically in PostgreSQL
I created a small database in PostgreSQL to manage the data for my thesis. I just started with Postgres, so this is probably a newbie question. I added a column in several tables, that is supposed to ...
0
votes
3answers
58 views
Indexing only for most recent entries in PostgreSQL
Consider the following table:
|-----------------------------------------------------|
| raffle |
|----|---------|----------|-----|---------------------|
| ...
0
votes
1answer
224 views
Store timeseries data in normal SQL database
I have got historic data of around 20 machine sensors with a time resolution of one second stored in csv-files which need to be imported to a SQL database prior to further data handling and analysis.
...
0
votes
1answer
214 views
current_timestamp at time zone 'UTC' SQL Server equivalent
What is the equivalent of the Postgres "current_timestamp at time zone 'UTC'" in SQL Server?
How do I select the current timestamp in UTC in SQL Server?
1
vote
1answer
30 views
Get durations from time series table where some condition holds consecutively
Given a table where each row has a timestamp and some measurement, how can I get all of the intervals during which the measurement consecutively satisfies some condition?
Here's a SQLfiddle (not sure ...
7
votes
1answer
120 views
How can I order rows returned by the RETURNING clause of a DML statement?
I am using this statement to get all the rows ordered by timestamp in ascending order
DELETE FROM @tableName
WHERE id = ANY (
SELECT id
FROM @tableName
...
0
votes
2answers
59 views
Why can't I insert this value using timestamp but can with datetime?
I want to insert this value: 2020-03-29 01:00:00
If I set up a column called date with data type datetime I can insert the value. But if I use the data type timestamp I cannot insert and get an error ...
6
votes
1answer
150 views
Does timestamp protocol following thomas's write rule allow non-view-serializable schedules in some cases?
I have come across the following line in a text book (Database System Concepts
Textbook by Avi Silberschatz, Henry F. Korth, and S. Sudarshan $6e$) page no. 686:
Thomas’ write rule allows schedules ...
0
votes
1answer
30 views
PosgreSQL query slow when sorting on indexed timestamp colum
I've the following issue: the table below:
CREATE TABLE public.current_read
(
id bigint NOT NULL DEFAULT nextval('current_read_id_seq'::regclass),
cube_id bigint NOT NULL,
end_time ...
0
votes
3answers
70 views
Generate random timestamp taking into account shift to daylight saving time
I want to generate random big data in MariaDB.
One of this columns is random timestamp which I generate like this:
select (current_timestamp
- interval (rand() * 60 * 60 * 24 * 365 * 10) ...
0
votes
1answer
66 views
TIMESTAMP OR DateUpdate
I want to implement incremental ETL on some of my sql server tables, so I need a new column like UpdatedDate to track change dates of my records. I want to transfer only updated and inserted records, ...
1
vote
2answers
346 views
Why isn't it possible to cast to a timestamp in an index in PostgreSQL?
I am working with a JSON document which I receive from an external source. In this document, there is a timestamp property which corresponds to the textual format of a timestamp PostgreSQL data type. ...
0
votes
1answer
179 views
Parse a unix/epoch value as a `TIMESTAMP` in PostgreSQL
PostgreSQL's COPY command is useful to quickly import lots of data, and the data must be in the textual representation for the datatype.
I am importing lots of data, includes a timestamp column, but ...
0
votes
1answer
42 views
Is there an underling difference between TIMESTAMP and ROWVERSION when creating columns with each?
I'm having problems with SSDT detecting a change to a "TIMESTAMP" column where both the target column and model.xml column definition are of type "TIMESTAMP". When this happens, the following alter ...
0
votes
0answers
20 views
Recognizing a timestamp
I am looking at some sample data and I noticed that the transaction datetime is 42,814,662,041,308. Can anyone help me convert it please?
I have already tried converting using Epoch Unix time
1
vote
1answer
175 views
DatetimeFieldOverflow when inserting more than three decimals precision [closed]
TIMESTAMP type should be fine (docs)
timestamp [ (p) ] [ without time zone ] 8 bytes both date and time (no time zone) 1 microsecond / 14 digits
Create the table like:
CREATE TABLE IF NOT ...
0
votes
2answers
66 views
how to use the same timestamp along several tables?
Lets say a have 2 tables and both have a datetime column.
On table A, on datetime column, lets say, ts1, is non null, default is CURRENT_TIMESTAMP and on update CURRENT_TIMESTAMP;
The other table, B,...
0
votes
1answer
41 views
Column with Default value as Sum of TIMESTAMP
I have a table which has 2 columns ( JobDate , RecordTime)
--------------------------------------
JobDate | RecordTime | SumCol
--------------------------------------
2019-07-20 | 2019-07-...
0
votes
0answers
88 views
Redshift/Athena: Most efficient way to cast timestamps of varying precision to date in query?
I'm receiving a stream of log files to an S3 bucket and querying this data through Amazon Athena (Redshift). Contained in the log files are timestamps with the seconds field having decimals to the ...
1
vote
2answers
724 views
How do I know if my PostgreSQL is up do date with DST rules?
Recently (2019-04-25) brazilian government ended the use of daylight savings time all around the country.
In my current project I rely on PostgreSQL to do timezone conversions and analytics based on ...
0
votes
1answer
35 views
Keep track of the last read record in MySQL
I have 2 databases in MySQL:
1) An input Latitude-Longitude_dB ('latlong_db', henceforth): It has the latitude and longitude of each reading from a GPS tracking device.
2) A Weather_db: I read the ...
0
votes
2answers
753 views
Get time from Unix milliseconds timestamp
My MySQL version is 5.6; it has a FROM_UNIXTIME function. But it works for timestamps with 10 digits: 1447430881.
mysql> SELECT FROM_UNIXTIME(1447430881);
-> '2015-11-13 10:08:01'
Now ...
2
votes
2answers
232 views
Should I split timestamp parts into separate columns?
I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later ...
3
votes
1answer
1k views
Oracle: Casting timestamp as date and comparing result
I don‘t really understand what cast(timestamp as date) is doing. I am on Oracle Database 11g Express Edition Release 11.2.0.2.0.
select cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') ...
1
vote
1answer
2k views
Creating index on TIMESTAMP column to use with range operators
TLDR: Can I create an index that's used by the following WHERE clause:
WHERE foo_date <@ tsrange('2018-01-01', '2018-02-01')
Let's say I have a table like this:
CREATE TABLE foo
(
foo_id ...
0
votes
0answers
177 views
In mysql system_time_zone doesn't reflects DST changes, isn't it a bug?
If a Ubuntu machine was in 'PST' timezone and on 10 march 2019 at 2:00 AM its timezone gets changed to 'PDT' timezone to accommodate DST changes then does this change is immediately reflected in ...
4
votes
1answer
3k views
Is there way to get transaction commit timestamp in Postgres?
I have data-pulling functionality that once in 5 seconds grabs all the data from Postgres table basing on modified_timestamp column. It works the following way:
SELECT * FROM my_table WHERE ...
0
votes
1answer
60 views
FUNCTION BEFORE TRIGGER INEXISTENT OPERATOR date > = double precision
In the 'pubblicazione' table I want to insert some fields of the tuple contained in the 'legge_approvata' table, but to do this the promulgation date must be in the same month and year as the date of ...
0
votes
1answer
32 views
Return hours, minutes and seconds left if column wasn't updated
I currently have this query:
UPDATE MyTable
SET next_update = now() at time zone 'utc'
WHERE next_update::timestamp < now() at time zone 'utc' - interval '1 day';
Which updates the column ...
0
votes
1answer
692 views
PostgreSQL compare a timestamp with timezone with a timestamp
How do you compare a timestamp with a timezone with a timestamp in postgresql?
I am trying to cast one of the 2 to the type of the other but it does not work for me. Eg.
Having
TS1: 2019-01-07 21:...
2
votes
1answer
50 views
Compute Leap Seconds
I use PostgreSQL 11 and have two columns. One is unix timestamp which is not aware of leap seconds and an utc timestamp which is aware of leap seconds.
Is there a way either to find the number of ...
2
votes
1answer
4k views
Incorrect datetime value: '0000-00-00 00:00:00' for column 'tmp_field_2' at row 1
I'm trying to run the following query:
INSERT INTO `user_domain_rating_days`
(
`user_domain_rating_id`,
`start`,
`...
1
vote
1answer
33 views
What happens if I load last 10 rows then next 10 after other rows are inserted?
What if I had a PHP script that had a pagination that loads last 10 rows then page two loaded next 10 in DESC order, but before loading next 10 on page 2, other 10 rows were added to the table?
What ...
4
votes
2answers
300 views
Different syntax to add/substract interval
Till now I used the following syntax to add an interval to a timestamp:
select now() + '5 year';
This worked fine till I tried to subtract the interval which results in a syntax error.
invalid ...
0
votes
2answers
113 views
WHERE clause on a TIMESTAMP column causes serious performance penalty
I have a pages and a page_views tables with 100k~ & 4M~ records respectively.
I run a query to get the top pages that have the most page_views:
select `pages`.`id`, `pages`.`host`, `pages`.`...
4
votes
1answer
3k views
“ERROR: timestamp out of range” converting epoch stored as bigint to a timestamp
I'm getting ERROR: timestamp out of range: "1.52701e+15" when trying to convert an epoch stored as a bigint into a timestamp (value is taken from a real database table):
select to_timestamp(...
