Questions tagged [timestamp]

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

Filter by
Sorted by
Tagged with
33
votes
3answers
112k views

How to get the timestamp column in only milliseconds from PostgreSQL?

I have a column "created" with type timestamp without time zone default now() in a PostgreSQL database. If I select colums, it has a nice and readable format per default: SELECT created FROM mytable;...
5
votes
1answer
14k views

How to get the difference in days between 2 timestamps in PostgreSQL

I have a table named accounts. I am interested in retrieving those accounts whose status has not been updated since more than 10 days. I thought about this query and it seemed to work but I am not ...
0
votes
1answer
42 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
14 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 ...
24
votes
2answers
37k views

How to best store a timestamp in PostgreSQL?

I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps. Assumptions Users in different timezones will use the database for all CRUD functions. I have looked at 2 ...
0
votes
1answer
26 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. ...
2
votes
1answer
50 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 ...
3
votes
1answer
6k 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-...
0
votes
1answer
34 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
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
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
498 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
3answers
28 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
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

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: '...
6
votes
2answers
11k 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
219 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?
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
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 ...
1
vote
1answer
198 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 ...
1
vote
1answer
179 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 ...
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
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
404 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 ...
47
votes
5answers
45k views

What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE?

There is a long and quite elucidating answer on the differences between TIMESTAMP WITH TIME ZONE -vs- TIMESTAMP WITHOUT TIME ZONE available in the SO post Ignoring time zones altogether in Rails and ...
1
vote
1answer
27 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
45 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 ...
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 ...
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 ...
0
votes
0answers
30 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. ...
3
votes
1answer
82 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
244 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
58 views

Indexing only for most recent entries in PostgreSQL

Consider the following table: |-----------------------------------------------------| | raffle | |----|---------|----------|-----|---------------------| | ...
0
votes
1answer
42 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
1answer
227 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. ...
1
vote
1answer
30 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 ...
0
votes
2answers
59 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
1answer
120 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
767 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 ...
6
votes
1answer
150 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
30 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
72 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
66 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, ...
1
vote
2answers
352 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
183 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 ...
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
66 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 ...
1
vote
2answers
728 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 ...

1
2 3 4 5