Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [timestamp]

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

0
votes
0answers
18 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
14 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 ...
1
vote
1answer
54 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
27 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
28 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
44 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
35 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
474 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
29 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
167 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
56 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
255 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
23 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
27 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
19 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
178 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
1k 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
83 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
464 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
304 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
32 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
192 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
108 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 ...
0
votes
1answer
1k views

Converting unix timestamp to an sql timestamp in postgres

I have passed Date.now() and other plain unix timestamps like 1534360109480 into sql and ran them through this. "@converted_date" = to_timestamp("@date") AT TIME ZONE 'UTC'; And it consistently ...
0
votes
0answers
35 views

Duration of consecutive timestamps by group in sql

I am new to psql window functions and would like to perform a query on test_table in order to find out every instance of inactive outages per mobile_id, country, and brand and most importantly the ...
-1
votes
1answer
430 views

MySQL - Change Default TimeZone changed all values in TIMESTAMP column

I have a few columns with the data type TIMESTAMP. Initially the timezone was default. So I have added the values like this. mysql> create table test (t1 timestamp, t2 datetime); Query OK, 0 rows ...
8
votes
3answers
4k views

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY? Is there a possibility that two or more different INSERTs, get the same CURRENT_TIMESTAMP?
3
votes
1answer
369 views

Is there a strategy to use timestamp as primary key?

The majority of times we use integer sequences for primary keys to maintain uniqueness of row in a table. Couldn't we use timestamps instead of integers for this purpose? Is there any flaw in that ...
0
votes
2answers
141 views

Postgres - using aggregate functions to return a different column?

I currently have a table containing raw temperature data that looks like this: I have written a query to find the min, max and avg of this data grouped by the date of the timestamp: drop table ...
0
votes
2answers
84 views

Mysql 5.6 general log file output not showing unix timestamp

Mysql 5.6 general log file output not showing unix timestamp, so i wonder if there an option that i can execute to enable unix timestamp while logging general queries ? UPDATE : Here is example for ...
1
vote
1answer
358 views

timestamp with timezone issue with TZR vs TZH:TZM

I have a Hibernate with Oracle JDBC based application inserting/updating into the following column; COLUMN_A TIMESTAMP WITH TIME ZONE DEFAULT systimestamp NOT NULL but while it automatically appends ...
0
votes
1answer
177 views

Timestamp and substring with regular expression

I am using postgresql and I have the following table Table "public.test" Column | Type | Modifiers --------+--------------------------+----------- name | text ...
4
votes
2answers
65 views

Get total amount of time between records when changed state

I have a table, showing the status (healthy, broken) for each device at a given timestamp. I need to get the total amount of time of a whole breakdown. So the data is stored like this: device_owner ...
1
vote
2answers
1k views

How are SQLServer Timestamp/RowVersion Values Created?

I am relatively fresh to SQLServer's use of ROWVERSION/TIMESTAMP data, and normally just query for required rows based on modified dates. How is the ROWVERSION assigned to a new row in a database? ...
3
votes
2answers
100 views

Joining tables based on time and distance: PostGIS with PostgreSQL?

I have a table with several thousand rows that contains time and position data. Rows with the same group_id have the same timestamp. Lets call this table "Small": Small Table: id event_time ...
2
votes
2answers
95 views

AT TIME ZONE direction changes based on column having a timezone [duplicate]

Why does Postgres do the opposite thing for TS with and without Timezones? In one case it adds hours, in the other case it subtracts. Just to confuse things more, when specifying GMT+x, the hours ...
1
vote
2answers
100 views

Docker for Oracle Timestamp out of sync

I have created an Oracle Database Docker Image with the help of this link SYSTIMESTAMP shows UTC time and not my local machine's time. kaushik:~ knayak$ date Fri Jun 8 17:46:35 IST 2018 select ...
3
votes
2answers
110 views

Is there a way to avoid having chained/nested “replace()” items when using an ISO 8601 timezone offset value in a generated column?

I am using MySQL 5.7 and have a table that has the following columns that are being used to store some data that is source from an Apache common log formatted access log; details extracted from a ...
0
votes
1answer
247 views

Batch update timestamp records to correct timezone

I'm looking to batch update some timestamp records in postgresql. My app was storing timestamps to the timestamp column (without timezone) in the the wrong timezone (BST). I only noticed a while ...
3
votes
3answers
537 views

Is timestamptz preferred when timezone logic is performed by the client?

I'm in the process of migrating a web application from SqlServer to PostgreSQL and I'm trying to figure out which type to replace datetime2 with. The general advice seems to be always use timestamptz,...
0
votes
3answers
3k views

Compare timestamp type columns in SQL Server

I am trying to get data since last timestamp. In my legacy database, all tables have a column named as recordStamp type timestamp. I want to rely on this to select records modified after the max(...
2
votes
1answer
752 views

Update value in timestamptz column

I have a table column with the data type timestamptz. Time zone of server is America/Denver. Going through some strange issue while inserting a value in that column. When I update the column to the ...
1
vote
1answer
290 views

Formatting Timestamp in Default Constraint

Can you format a TIMESTAMP WITH TIME ZONE column in a CREATE TABLE default constraint in Postgres v10.x? Example Code: create_date TIMESTAMP WITH TIME ZONE DEFAULT to_char(current_timestamp, 'DD-...
3
votes
1answer
63 views

Return values at more/less specific time points

In PostgreSQL 10.0, I have a big table with two columns: CREATE TABLE xs ( ts timestamp with time zone NOT NULL, x integer NOT NULL ) Now, if I have 100 given time points, how do I return ...
1
vote
1answer
3k views

DB2 - Inserting a timestamp operation result in a timestamp field [closed]

I got a some javascript Dojo code assembling a query from JSON variables and I have been trying to insert a timestamp in it. This is my test table (schema.mdummy): k and v are varchars n is an ...
1
vote
2answers
64 views

Is there a way to specify an table/index is sorted by more than one column?

Suppose I have a table: CREATE TABLE [dbo].[TestTable] ( [Id] INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1), [Timestamp] DATETIME2(7) NOT NULL DEFAULT (SYSUTCDATETIME()), [Data] INT ...
2
votes
1answer
3k views

How can I set a default now() timestamp in a timestamp array?

I want to have a timestamp array which tracks a series of events. The first event always happens when the row is created, so I want the array to be instantiated with an initial default value. I've ...
1
vote
1answer
898 views

How can I change TIMESTAMP to TIMESTAMP(3)?

I have a column of TIMESTAMP, NOT NULL, DEFAULT CURRENT_TIMESTAMP. I want to modify it as TIMESTAMP(3). I tried ALTER TABLE <table> MODIFY COLUMN <column> TIMESTAMP(3) NOT NULL DEFAULT ...
0
votes
1answer
895 views

Cursor Based Pagination for Timestamp field with non-unique values

We are trying to create a query for the following table : CREATE TABLE `Action` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `vendor_Id` int(10) unsigned DEFAULT NULL, `name` varchar(60) ...
1
vote
1answer
417 views

Find a closest match in the same table

I have a table with timestamped data. For all the rows, I want to find the row that is the closest to 20 minutes after and between 15 and 25 minutes after. e.g. if the table is like CREATE TABLE foo(...