Questions tagged [timestamp]

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

Filter by
Sorted by
Tagged with
3
votes
3answers
834 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,...
1
vote
3answers
5k 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
2k 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
514 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
5k 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
4k 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
1k 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
1k 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
492 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(...
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 ...
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 (...
3
votes
1answer
6k 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
644 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
6k 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
571 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 ...
3
votes
1answer
23k 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
413 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
2k 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
425 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::...
3
votes
1answer
10k 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
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
2answers
771 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
32 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
2k 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 '...
5
votes
1answer
2k 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
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 ...
1
vote
2answers
1k 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 ...
0
votes
1answer
984 views

GMT timezone in Mysql timestamp literal value

In brief We want to input value for a timestamp column WITH timezone value in the literal string e.g. '201708-09 10:11 GMT +07:00' In full We have a sample table create table some_table ( ...
1
vote
1answer
42 views

Optimize MySQL Table for Charting

Our application is collecting data points at different rates (between 500ms - 10,000ms) depending on user settings and that data is queried to plot points on a chart. I started with the following ...
5
votes
2answers
7k views

How to change only time while keeping the date same in timestamp type

I have timestamp column used for scheduling calendar events. I would like to change only the time part while retaining the date part untouched. How can I possibly do it? I'm on postgresql Current: ...
0
votes
1answer
35 views

Automatically add core fields to a table when created

I was reading these articles on StackOverflow regarding triggers and created date and updated date in mysql, and was wondering whether it is possible to create a trigger that checks if these two ...
0
votes
1answer
71 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 ...
2
votes
1answer
1k views

Conversion from varchar2 to timestamp

In a oracle table column, there is a varchar2 column which contains value '2017-02-14T18:04:34.988+00:00' I need to convert it to timestamp. I am using TO_TIMESTAMP() to convert but I am not able to ...
2
votes
3answers
451 views

What is the point of the Script Date in a stored procedure script generated by SSMS?

When I generate a stored procedure script, SSMS additionally generates a comment containing the name of the object and a Script Date timestamp: The timestamp is just the current time at the moment ...
3
votes
3answers
226 views

Calculations using two tables with different date frequencies

I'm looking to align dates in one table with dates in another table that have different frequencies. I want the values associated with the low frequency table to repeat until there is a new date so I ...
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 ...
0
votes
1answer
84 views

How to determine if a timestamp in one table is between two timestamps in a subquery of another table?

I have two tables - one titled "index" which contains the 'datetime' timestamp with time zone, and one titled "planning" which contains the 'start_time' and 'end_time' timestamps. Here are the schema ...
3
votes
1answer
833 views

Transfer microsecond timestamp into table using COPY

I need to transfer a microsecond timestamp into a table by using the COPY command. My approach using a normal INSERT, where X is a timestamp (since 1.1.1970) in microseconds, looked like this: ...
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 ...
3
votes
1answer
8k views

Why does MySQL calculate a wrong timestamp when using FROM_UNIXTIME

I have debugged this a lot and finally managed to reproduce it in the mysql shell. I am storing timestamps in a mysql database with the field type timestamp. I use FROM_UNIXTIME() to update them from ...
2
votes
2answers
1k views

postgres - SUM Time of this Interval and Group by 30 and 30 minutes

I need to SUM Time of this Interval and Group by 30 and 30 minutes. CREATE TABLE foo AS SELECT date_start::timestamp, date_end::timestamp, agent FROM ( VALUES ('2017-01-06 06:52','2017-01-06 06:...
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 : ...
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'::...
1
vote
2answers
58 views

How to set up last access (timestamptz) in table 1( basic info table) when working with many other tables having foreign key from table 1?

Let's say the database have 5 tables for sake of simplicity (there're many more in reality). Table 1 has the basic information of a user which is not changed so often. However, the other tables are ...
2
votes
1answer
3k views

Trigger to update a column in a new record to CURRENT_TIMESTAMP as it is inserted

Ok, so I'm trying to update a column to a CURRENT_TIMESTAMP in a specific record whenever the record is originally inserted. This is what I've got but otherwise I am lost. CREATE TRIGGER dbo....
3
votes
2answers
463 views

MySQL - Unix timestamp from columns with hour, day, month

I have MySQL table with columns (day, month, hour). Is it possible to build unix timestamp from this within the query itself? I am looking for something like this: SELECT unix(2016, a.month, a.day, ...
2
votes
1answer
996 views

Syntax Error - OUTFILE with Timestamp

I have managed to come up wit the below script for exporting some files. This needs to be done on a number of datasets, and is done daily. The problem I am having is the I am getting the following ...
2
votes
1answer
184 views

How to make 6 partitions of table by 6 months and 7th partion of older than 6 month data?

we have a table structure with periodical data in a time-stamp column. how to make 7 partition of this table with 7th portion as data older than 6 months. I have already thought of running a cronjob ...