Questions tagged [timestamp]
For questions about timestamp data types and/or recording a particular point in time.
225
questions
0
votes
3answers
31 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
43 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
32 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
16 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
0answers
22 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
22 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
44 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
31 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
17 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
0answers
19 views
How to add SDC timestamp columns in SAP BW?
I have activated my data source and data is in PSA. I can only load the all.
Should I add timestamp columns(For SDC types) in PSA or after DSO?
How to do it?
1
vote
2answers
70 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
31 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
1answer
195 views
MySQL - how to 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
189 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
202 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
333 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
54 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
954 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
43 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
263 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
39 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
2k 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
186 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
87 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
1k 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
25 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
29 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
470 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
3k 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
123 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
880 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
526 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
38 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
294 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
124 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
2k 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
49 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
1k 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 ...
10
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?
4
votes
1answer
706 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
392 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
190 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
763 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
331 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
78 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 ...
