Stack Exchange Network

Stack Exchange network consists of 174 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
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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

0
votes
1answer
29 views

Calculate Total Minutes of attendance in MySql

I have a design a table for attendance and here are the genral fileds AttendanceMachineLoginId EmpId AttendanceDateTime Whenever user come to office he has to make his attendance. The first ...
1
vote
2answers
80 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 ...
-1
votes
1answer
18 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 ...
-1
votes
0answers
31 views

Why does the timestamp format change after I fetch it from table? [migrated]

My table's structure: +------------+---------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--...
0
votes
0answers
20 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
25 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
3k 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?
2
votes
1answer
57 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
44 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
18 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
51 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
16 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
57 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
251 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
51 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
62 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
51 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
74 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
42 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 ...
2
votes
3answers
110 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
538 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
89 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
84 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
58 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
675 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
61 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
707 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 ...
0
votes
0answers
336 views

MySQL SELECT query displays TIMESTAMP values with zero month, day, hour, minute, and second

I am attempting to insert some TIMESTAMP values into a MySQL table. While I can successfully insert a TIMESTAMP value into the relevant field, when running a simple SELECT query on that data, MySQL ...
0
votes
0answers
20 views

What does the Read Timestamp of a data item store?

"R-timestamp(Q) denotes the largest timestamp of any transaction that executed read(Q) successfully." Does the above statement mean "read-timestamp of data item Q will store timestamp of the ...
1
vote
1answer
288 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
448 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
266 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(...
0
votes
2answers
1k 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 ...
2
votes
1answer
642 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 (...
1
vote
1answer
644 views

dual table postgres doesn't return timestamp

I am trying to select now() from my local postgres database dual table: select now() from dual; It returns no rows. Just prints column name now(yyyy-MM-dd HH:mm:ss.ffffff) What is it that I am ...
1
vote
2answers
108 views

Timezone offset displayed though it shouldn't be stored

I new to Postgres and v9.6.3 on a Linux system. From the docs and the PostgreSQL: Up and Running book, I learned that the timestamptz type just subtracts the offset of a timestamp on insertion and ...
2
votes
1answer
1k views

Postgresql update timestamp without timezone to have timezone

When I created the table, a Timestamp column without timezone was created. All the imported time is in UTC. Now I want to add the timezone information explicitly, and I have altered the column ALTER ...
0
votes
1answer
255 views

Sum duration for each day of a month

Question: I would like to create a query that can return a list of all days of the month with a sum of all duration for each day including zero for each day that has no data. Database: PostgreSQL 9.x ...
2
votes
1answer
5k views

PostgresSQL date/time field out of range on seconds-since-epoch

I am importing a csv file into a PostgreSQL database. Most of the file imports without any trouble until I get this message ERROR: date/time field value out of range: "1421088300" HINT: Perhaps you ...
1
vote
2answers
171 views

MYSQL ways to represent '1970-1-1 0:0:0'

Out of interest, are there any other ways of representing the timestamp '1970-1-1 0:0:0' other than from_unixtime(0)? In PostgreSQL there is 'epoch'::timestamp. Would there be an equivalent in MySQL ...
1
vote
1answer
366 views

MySQL groupby time time difference

I have a data like this id timestamp ticket_id 2 6/16/16 8:14 1466089815 10 6/27/16 7:15 1466089815 3 6/16/16 8:14 1466089816 11 6/27/16 7:15 1466089816 13 6/27/16 7:50 ...
3
votes
2answers
107 views

Extracting sum of time in a period

I have a login table, which registers datahora_ini (system entry time) and datahora_fim (system exit time). Sample data: CREATE TABLE login(cod_user,datahora_ini,datahora_fim) AS VALUES ( 101::...
1
vote
1answer
4k views

How do I generate a date series in PostgreSQL?

If you're looking to generate a time series, see this question Let's say that I want to generate a series of dates between two dates. I see the function generate_series provides only Function ...
0
votes
1answer
76 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
2answers
363 views

UNION ALL date on the same row

I am unioning multiple tables together in Postgres, where the timestamp is slightly different, but the same if I round to the hour. Is it possible to make each union show on the same row, if the date ...
1
vote
2answers
29 views

Complete Series From Partial Series

I'm struggling to come up with an answer to this problem and so hope to find some help here. I have the following time series data table with missing entries: CREATE TABLE foo AS SELECT timestamp::...
0
votes
2answers
591 views

SQL timestamp to specific hour

I need to set the time of a timestamp in SQL. My results need to be between yesterday at 7 o'clock and today at 7 o'clock. Here is the query WHERE created_at BETWEEN ?::timestamp - INTERVAL '...
4
votes
1answer
664 views

Postgresql partitioned table timestamptz constraint problem

The table reports is partitioned table by day like reports_20170414,reports_20170415 Constraint SQL is defined as follow CHECK ( rpt_datetime >= '2017-04-14 00:00:00+00'::timestamp with time ...
0
votes
1answer
559 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 ...
1
vote
2answers
614 views

Select date based on the daylight saving periods

Imagine that I have this table: Id | Name | Timestamp ________________________________________________ 1 | Bill Gates | 2017-01-10 12:00:00 ...