Questions tagged [timestamp]

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

Filter by
Sorted by
Tagged with
0
votes
3answers
26 views

What is the difference between these extracting date method?

Assuming I have a table called MyTable, with a TimeStamp field "Created". I want only extract the Date part of this column, and in my research I found these alternatives: SELECT created AS &...
-1
votes
2answers
61 views

Purpose to use timestamp datatype in sql server? [closed]

What is the purpose to use timestamp datatype in sql server? Do you have a concrete example of using timestamp in real business case? When is it relevant to use timestampe datatype? Thank you!
0
votes
1answer
12 views

sql backup restore converts createdAt to local TZ

I have a remote MySQL DB in the cloud on JAWSDB. I have createdAt and updatedAt timestamps for records that are in UTC TZ. I'll backup these DBs using mysqldump -h HOST -u USER -p > backup.sql. I ...
0
votes
1answer
22 views

Select count of timestamp formatted field in short date format to give number of rows

I have a field titled DTUpdated and it is a timestamp field. I am trying to select a count of the dates in the rows that are xx old. i.e. 0-3 months, 3-6 months etc. I am not at the writing date ...
1
vote
1answer
20 views

What value should I choose as length for timestamp without timezone in PostgreSQL?

I'm currently setting up a database to populate it with big data. All the data I get comes from Valve's Steam Web API. Therefore, as all timestamps are returned in Unix timestamps, I cannot directly ...
0
votes
0answers
29 views

Is it possible to record read access to a row?

A table has a creation timestamp column with DEFAULT and an update timestamp column with DEFAULT ... ON UPDATE .... Similar to the ON UPDATE|DELETE ... constraint, we're looking for a way to have a ...
0
votes
2answers
37 views

use of age() function postgres [closed]

I am trying to calculate the time since an event took place. The event timestamp will itself be the result of a MIN aggregate function. Is there a way to do this. I have run a query to get the ...
0
votes
1answer
65 views

When I set Datestyle to 'SQL, YMD' all queries still return as 'SQL, MDY'

I am working on a database migration from Oracle 12c to Postgresql 12. We must keep the same date format as the program interacting with this database is written in COBOL and should not be changed. ...
0
votes
0answers
24 views

Fix the timezone of timestamp in MySQL

From my misunderstanding, I stored time as timestamps and thought they were stored as UTC+0. But I moved into a different country and found the timestamps were stored in the local time. The issue is ...
0
votes
1answer
89 views

in which timezone Psql displays timestamp without timezone (PostgreSQL)?

What I mean is the following: if I create a timestamp without timezone (timestamp) column and store there some value, internally microseconds are stored, but when issuing select this is converted to a ...
0
votes
1answer
30 views

How can I add timestamp into my table when I use Write-DbaDbTableData?

I am trying to use one table to store the SQL server disk space checking data by dbatools: $allservers_PRD | Get-DbaDiskSpace | Write-DbaDbTableData -SqlInstance MySQL2019 -Database SQL_DBA_TASK -...
0
votes
1answer
48 views

SQL - Spilt timestamp into multiple rows

I am dealing with data that needs to be looked at on a shift-to-shift basis (8:00:00 to 20:00:00 and its reciprocal are the two shifts) There are instances where a timestamp (one row) will span longer ...
1
vote
0answers
30 views

Correcting wrong timezones/timestamps in existing tables

I did a huge mistake because I didn't know how the TIMESTAMPTZ datatype works in postgresql. We need to store sensor data from a handful of different sources. We have a few tables with two timestamp ...
2
votes
1answer
17 views

Using Postgres param in a function

I'm trying to create a few functions to make the SQL queries between my SQLite and Postgres DB's the same. One thing I would like to do is change a timestamp to a double via a function such as: create ...
2
votes
1answer
70 views

MySQL Best approach for recording last update timestamp for every column and every row

This question is about possible approaches for time stamping every column of every row in a table. I have a table with a small number of rows (fewer than 200 and not likely to grow much larger). Each ...
0
votes
0answers
14 views

Column with timestamp datatype not having proper value

I am using PostgreSQL v9.5 A column utime with datatype timestamp without timezone is not having proper values for some rows as given below: select utime from mytable where utime is not null order by ...
0
votes
1answer
374 views

Postgres epoch from current_timestamp

I've learned that epoch (UNIX timestamp) is a unique independent amount, so I am baffled by output I get from the following queries: select extract( epoch from current_timestamp::timestamp with time ...
0
votes
1answer
16 views

Querying the most recent records with two fields: datestamp and timestamp

I have a table called airquality that has records for air quality measurements taken on different days and times within those days. These date/timestamps are stored in two different columns: validdate ...
0
votes
1answer
62 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
3answers
34 views

Convert two integers into timestamp

How do I convert two integers, one for date and one for time, into a timestamp? Given 20200304 and 1046, return a timestamp for April 3, 2020, 10:46 AM. Given 20200304 and 946, return a timestamp for ...
0
votes
0answers
32 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
26 views

Migrating timezone timestamps

I am trying to migrate this node library https://github.com/LocalSEOGuide/lighthouse-reporter from Postgres to MySQL and getting an error error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '...
0
votes
1answer
40 views

How can I output a legible timestamp from “1583981745.335346” (stored in a 'double' field type) via a select statement?

I have a table within a WordPress installation that stores timestamps in a format that I do not recognize, nor can I figure out how to translate it. In the MySQL database, it appears as "1583981745....
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 ...
1
vote
1answer
902 views

mongodump command with timestamp query

When I execute the below query in mongodb shell, it gives me the number of records updated after the mentioned timestamp. db.getCollection('oplog.rs').find({"ts":{"$gt":Timestamp(1582823397,2)}}) I ...
0
votes
0answers
34 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 ...
1
vote
1answer
148 views

Update record with converted epoch timestamp from another column

I'm trying to convert epoch data in one column (timestamp_epoch) to a legitimate timestamp in another column (timestamp) for every record in a table. In reading the PostgreSQL Documentation it ...
2
votes
1answer
62 views

Preserving timezones explicitly in postgres

I've been doing quite some research on different ways of 'seeing' time and how to properly map it in Postgres but I'm still not certain on what to actually use. Several articles recommend or rather ...
3
votes
1answer
370 views

Why mysql throws “invalid default value for field” only when two or more timestamp fields exist in the create statement?

In mysql 5.7.28 I create a table like this: create table t1 ( id int not null, d1 timestamp) engine=innodb; which works fine and creates d1 as non-null with default value current_timetamp,on update ...
0
votes
2answers
2k views

Add timestamp to field automatically in PostgreSQL

I created a small database in PostgreSQL to manage the data for my thesis. I just started with Postgres, so this is probably a newbie question. I added a column in several tables, that is supposed to ...
0
votes
3answers
78 views

Indexing only for most recent entries in PostgreSQL

Consider the following table: |-----------------------------------------------------| | raffle | |----|---------|----------|-----|---------------------| | ...
0
votes
1answer
775 views

Store timeseries data in normal SQL database

I have got historic data of around 20 machine sensors with a time resolution of one second stored in csv-files which need to be imported to a SQL database prior to further data handling and analysis. ...
0
votes
1answer
1k views

current_timestamp at time zone 'UTC' SQL Server equivalent

What is the equivalent of the Postgres "current_timestamp at time zone 'UTC'" in SQL Server? How do I select the current timestamp in UTC in SQL Server?
1
vote
1answer
31 views

Get durations from time series table where some condition holds consecutively

Given a table where each row has a timestamp and some measurement, how can I get all of the intervals during which the measurement consecutively satisfies some condition? Here's a SQLfiddle (not sure ...
7
votes
1answer
121 views

How can I order rows returned by the RETURNING clause of a DML statement?

I am using this statement to get all the rows ordered by timestamp in ascending order DELETE FROM @tableName WHERE id = ANY ( SELECT id FROM @tableName ...
0
votes
2answers
95 views

Why can't I insert this value using timestamp but can with datetime?

I want to insert this value: 2020-03-29 01:00:00 If I set up a column called date with data type datetime I can insert the value. But if I use the data type timestamp I cannot insert and get an error ...
7
votes
2answers
223 views

Does timestamp protocol following thomas's write rule allow non-view-serializable schedules in some cases?

I have come across the following line in a text book (Database System Concepts Textbook by Avi Silberschatz, Henry F. Korth, and S. Sudarshan $6e$) page no. 686: Thomas’ write rule allows schedules ...
0
votes
1answer
32 views

PosgreSQL query slow when sorting on indexed timestamp colum

I've the following issue: the table below: CREATE TABLE public.current_read ( id bigint NOT NULL DEFAULT nextval('current_read_id_seq'::regclass), cube_id bigint NOT NULL, end_time ...
0
votes
3answers
199 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
95 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, ...
2
votes
2answers
900 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
402 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 ...
1
vote
1answer
75 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
23 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
436 views

DatetimeFieldOverflow when inserting more than three decimals precision [closed]

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 ...
1
vote
2answers
102 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
48 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-...
1
vote
2answers
1k 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
38 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 ...
2
votes
2answers
1k views

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 ...

1
2 3 4 5 6