Questions tagged [timestamp]
For questions about timestamp data types and/or recording a particular point in time.
235
questions
0
votes
0answers
10 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
31 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
42 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
55 views
Indexing only for most recent entries in PostgreSQL
Consider the following table:
|-----------------------------------------------------|
| raffle |
|----|---------|----------|-----|---------------------|
| ...
0
votes
1answer
41 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
29 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
29 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
119 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
27 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
126 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
29 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
38 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
55 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
134 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
71 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
34 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
19 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
50 views
DatetimeFieldOverflow when inserting more than three decimals precision
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
46 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
34 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
31 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
392 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
33 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
455 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
209 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 ...
2
votes
1answer
539 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
791 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
117 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 ...
0
votes
0answers
16 views
How to create an optimized view that fills in sparse observations with modeled data?
I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int which is partitioned by month (e.g.: observations_201903). There are approximately 150k sensors, providing ...
3
votes
1answer
2k 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
50 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
30 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
433 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
43 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
3k 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
30 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
216 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
91 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
2k 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(...
0
votes
1answer
26 views
Postgresql filtering by value over rolling time period
I am newbie to postgresql and still getting my head around. I have used windows function etc. till date and yes I can make some sense.
I have got the following data set:
I need to find those ...
0
votes
1answer
31 views
I need help creating an index for timestamp
I have a query taking too long.
This is the table:
mysql> describe parking_data;
+----------------+--------------+------+-----+-------------------+----------------+
| Field | Type ...
1
vote
0answers
21 views
Query that fetchs average values over timestamp range of time, on a certain value of another pair of fields
This is the situation, over the time our table reached around 800 millions rows. And it is going to get bigger and bigger. It is a service that collects metrics of energy (voltage, power, current, etc)...
3
votes
1answer
599 views
How to compare timestamps with different time zones?
For example I have a table with timestamps:
2018-04-05 06:00:00 +01
2018-04-05 06:00:00 +00
2018-04-05 06:00:00 -01
2018-04-05 04:00:00 -01
And I want to get entries that are ...
...
3
votes
1answer
5k views
Convert YYYY-MM-DD date (without time zone) to unix timestamp in Postgres
I've got a column of type date with the following value format: YYYY-MM-DD (eg. 2018-08-03). I want to get the unix timestamp equivalent of the dates, but without the timezone.
F.e. the date 2018-08-...
1
vote
2answers
141 views
How can I use Spatial Indexes for timestamps (non-spatial 1d data)?
I'm using MySQL 8.0. I have some table containing TIMESTAMP column and I need to select within TIMESTAMP range:
SELECT *
FROM t_tbl
WHERE ts BETWEEN @someDate1 AND @someDate2
I want to make this ...
2
votes
1answer
1k views
Error while inserting into array of timestamps
Im using postgres 10.1 and the Datestyle config is the following
DateStyle
-----------
ISO, MDY
Im having this error
ERROR: date/time field value out of range: "1535673858" Hint: Perhaps
you ...
4
votes
1answer
628 views
Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?
Setting time zone as numeric offset (without abbrev, thus IMO not in POSIX format) is still interpreted by PostgreSQL as POSIX format:
SET TIME ZONE '+02:00';
At least I assume, because checking the ...
3
votes
1answer
40 views
Why does postgresql differentiate between named and numeric time zone notations? [duplicate]
Why is there a difference between named timezone and numeric timezone offset, both of which yield different UTC timestamps for TIMESTAMPTZ?
Assume Europe/Prague timezone, which is currently +02:00 (...
0
votes
1answer
332 views
Calculate Total Working Minutes in MySql
I have a design a table for attendance and here are the genral fileds
AttendanceMachineLoginId
EmpId
AttendanceDateTime
Whenever user will come to office, he has to make his attendance.
The first ...
1
vote
2answers
136 views
What is the best smallest data type size for 19 digit number?
I'm using this code to get a mix of Date and Time to use it like a unique ID
DateTime.Now.ToString("yyyyMMddHHmmssfffff")
In SQL Server 2017 I'm confused as to which best smallest size of data type ...
