Questions tagged [timestamp]

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

29 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4
votes
1answer
1k 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 ...
3
votes
0answers
2k views

Logic to convert a UTC date-time to a specific time zone

I have a table which has 1 row per event. Each event has a datetime stamp column which has values in UTC time. Each row has an associated local timezone in which the event occurred. I have a DIM ...
1
vote
0answers
26 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)...
1
vote
1answer
1k 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 ...
1
vote
0answers
1k views

MySQL FROM_UNIXTIME() gives wrong date in certain time zones?

The timestamps 1382836533 and 1382832933 correspond to the same wall clock time 2013-10-27 02:15:33 in cet timezone (because of daylight savings time). Therefore the conversion with FROM_UNIXTIME is ...
1
vote
0answers
180 views

compare time difference between current time with last data timestamp

I have a data server recording data every second. i need to setup a cron job that that runs a script that can give a reply if the now() is 24 hours more than the last data recorded. So that I can ...
1
vote
0answers
171 views

Output datetime from MySQL with offset

I'm currently writing to a datetime XML field from MySQL data. The MySQL field is populated with current_timestamp() but this only outputs - 2014-02-18 15:34:02 whereas I need something like 2014-02-...
0
votes
1answer
27 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
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

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 ...
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 ...
0
votes
0answers
31 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. ...
0
votes
1answer
43 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
0
votes
2answers
67 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 ...
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
0answers
178 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
1answer
30 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
408 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 ...
0
votes
0answers
80 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 ...
0
votes
1answer
287 views

Insert Timestamp on value change from other table

I have an EvidenceTimeStamp table that is triggered with data when the case table has data inserted into it. EvidenceTimeStamp: CaseID EvidenceNum EvidenceType Evidence_Initiated_Timestamp ...
0
votes
1answer
3k views

mysql - query to fill in missing datetime values

I have a Table similar to: CREATE TABLE `stats` ( `stat_ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `stat_Start` timestamp NULL DEFAULT NULL, `stat_values` smallint(5) unsigned DEFAULT ...
0
votes
1answer
98 views

Transaction - Timestamp Ordering. When is aborted transaction restarted

(I don't know if this is the correct place to ask, since this is more like a theoretical question). I have a question about restarting aborted transactions. I don't know when / at which point should ...
0
votes
1answer
1k views

Phpmyadmin feed a timestamp column with “0000-00-00 00:00:00”?

in PhpMyAdmin (OVH Mutu), a table with column type timestamp. Impossible to put into this colum a timestamp : Always display datetime like 2017-01-09 23:02:01 !!! It generate several problmes : ...
0
votes
1answer
22 views

Distinct with timestamp and miliseconds

How can I perform a distinct when the system displays millisecond in my timestamps? I just need the date. My query is like this: select distinct orders.client_po, orders.order_code,...
0
votes
1answer
499 views

localtimestamp or curent_timestamp for default value

In Oracle-12c I have some columns that I am converting from timestamp with time zone to timestamp with local time zone. Some of those same columns also currently have a default value of ...
0
votes
1answer
45 views

Constrain Partial Values in Timestamp

I'm working on a some Create Table statements for a project I have. I have two columns (StartDate, EndDate) where the data type is Timestamp. One of the requirements is to ensure that the date is ...