Questions tagged [timestamp]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
14 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
1answer
2k 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
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
462 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
2answers
42 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
22 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
5 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
15 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
25 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
151 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
275 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 ...
44
votes
2answers
39k views

Difference between now() and current_timestamp

In PostgreSQL, I use the now() and current_timestamp function and I see no difference: # SELECT now(), current_timestamp; now | now ------------...
0
votes
1answer
880 views

How to get the difference between two timestamps in minutes?

Demo create table my_table (ts1 timestamp,ts2 timestamp); insert into my_table (ts1,ts2) values (timestamp '2017-01-29 07:35:14.52',timestamp '2017-01-29 15:01:59.14'); I'm familiar with the ...
4
votes
1answer
605 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
1answer
115 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 ...
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 ...
1
vote
1answer
616 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 ...
9
votes
3answers
18k views

Storing time with milliseconds in database

Just a quick question regarding storing "time" in a database. I'm logging the time of users runs which are in the format 00:00:00:00 (hours, minutes, seconds, milliseconds). I was originally going to ...
2
votes
2answers
178 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
91 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') ...
15
votes
2answers
95k views

Can't default date to CURRENT_TIMESTAMP in MySQL 5.5

I am not able to set Current_timestamp as default value. My Mysql version is 5.5.47. Query is ALTER TABLE `downloads` ADD `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ; While it is working ...
1
vote
1answer
150 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
47 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
571 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
35 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 ...
3
votes
2answers
3k views

Poor query performance over timestamp range by week in PostgreSQL 9.3

I have a slow query that generates a report of account activity per week over the past year. The table currently has nearly 5 million rows and this query currently takes 8 seconds to execute. The (...
0
votes
1answer
29 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 ...
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 ...
0
votes
1answer
209 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
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
178 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
86 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
771 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(...
1
vote
2answers
5k views

How to sync MySQL's time with system time without mysql restart?

On my machine I have a service to synchronise the SYSTEM time (including Time zone), and would like the DB will synchronize with the machine time once in a while. When I change SYSTEM time, MySQL ...
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
20 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
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 ...
3
votes
2answers
8k views

Why is add timestamp column with default not null so slow in Oracle 11g?

In Oracle I need to add a new column with type timestamp with time zone to a large table (80 million rows). I cannot have more than 30 - 60 seconds of downtime when I run it in production. This ...
1
vote
2answers
103 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 ...
1
vote
0answers
20 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)...
5
votes
1answer
1k views

Oddities with AT TIME ZONE and UTC offsets

I don't understand the difference between these two columns. America/Chicago timezone is UTC-6, so I expect both to return the same result: select timezone('America/Chicago', '2017-01-01 12:00:00'::...
3
votes
1answer
394 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-...
2
votes
1answer
734 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 ...
3
votes
2answers
136 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 ...
4
votes
1answer
468 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
37 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 (...
1
vote
2answers
118 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 ...