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
1 answer
30 views

Why does PostgreSQL keep resetting my date to the wrong "timezone", with "seconds", as I try to remove it?

I asked this question at first: https://stackoverflow.com/questions/71979003/why-does-strtotime-get-confused-about-timezones-instead-of-ignoring-them-an I have this timestamp with timezone column ...
user avatar
  • 111
0 votes
1 answer
54 views

Time zone lost when returning timestamp with time zone from a function

I have this function: CREATE OR REPLACE FUNCTION laborable_day(dtDateTime TIMESTAMP WITH TIME ZONE) RETURNS TIMESTAMP WITH TIME ZONE AS $$ DECLARE _isHoliday BOOLEAN; BEGIN LOOP SELECT COUNT(*...
user avatar
0 votes
1 answer
73 views

Why do I see the timezone offset displayed in PgAdmin, for a timestamp with timezone

I'm using PgAdmin 4 v6, and for some timestamp data with data type of timestamp with time zone, I'm seeing it in PgAdmin like this: 2022-03-06 16:11:33+04 Why is the +04 being displayed? I've looked ...
user avatar
0 votes
1 answer
38 views

Goldengate timestamp (FFF)

Working with Goldengate 19.1.0.0.211019 replicat inbound to Oracle 19.0.0.0.0 consuming a trail file incoming from Nonstop (ie a non-Oracle database). Column in question from Nonstop is a string ...
user avatar
  • 476
0 votes
0 answers
248 views

MySQL 8.0 timestamp CURRENT_TIMESTAMP sets date to '0000-00-00 00:00:00' (was ok in 5.7)

The table T has this field +---------------+--------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra ...
user avatar
  • 395
2 votes
2 answers
459 views

Does the timestamp type have (hidden) milliseconds?

The updated column was created as | updated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | a simple SELECT gives +---------------------+ | updated | +--------------...
user avatar
  • 395
1 vote
1 answer
56 views

Storing timezoned HH:MM in PostgreSQL

My application needs to send a daily email to users at a time of their choosing, the timezone is also defined by the user itself. What's the best way to store HH:MM + timezone? And how can I ...
user avatar
0 votes
1 answer
262 views

How to GROUP by month & year with an epoch timestamp in AWS Redshift

Assuming a very simple schema as pictured below. (unfortunately the column is actually named timestamp) How do I simply get a count on records grouped by YEAR & MONTH
user avatar
1 vote
1 answer
432 views

Time zone conversion in PostgreSQL without timezone does the opposite operation

Query: SELECT TO_TIMESTAMP('2021-10-24 20:00', 'YYYY-MM-DD hh24:mi') as d, (TO_TIMESTAMP('2021-10-24 20:00', 'YYYY-MM-DD hh24:mi') at time zone 'utc')::timestamptz at time zone 'ASIA/CALCUTTA' ...
user avatar
1 vote
1 answer
57 views

Convert Varchar to Interval

I have a Postgresql column that is a varchar that is saving a string that looks like '2 years, 5 months' or '3 months' or '9 years, 0 months'. I can't convert the column to an interval because it's ...
user avatar
0 votes
3 answers
44 views

Statement of total precipitation for each hour, day or month

I store the data from the rain gauge in mySQL and needed help with listing the data. I save the data every 5 minutes and use the save date in the form of a timestamp. At the end of each hour, ...
user avatar
-2 votes
1 answer
107 views

query to get start time and end time from same column based on boolean value order by primary key [closed]

As of now, this is my current query: SELECT MAX(CASE WHEN status = 1 THEN [timestamp] END) as machineOn ,MAX(CASE WHEN status = 0 THEN [timestamp] END) as machineOff FROM machine_table where ...
user avatar
  • 11
0 votes
0 answers
38 views

How can I get all rows whos timestamp is closest to midnight (sampling timeseries)

I have a table that counts the amount of user interaction with a given item. Basically it has a table with 3 rows (item_name, time, num_interactions). The number of interactions never decreases, it ...
user avatar
7 votes
2 answers
596 views

Is there a performance or storage space advantage to lowering time/timestamp precision in PostgreSQL?

PostgreSQL allows time/timestamp to specify a precision: time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds ...
user avatar
-1 votes
1 answer
476 views

PostgreSQL Query - how to subtract timestamps and format durations (with builitin functions or otherweise)

I am looking for a solution to a problem with a select statement where I am trying to display the sleep duration. Sleep Table Here is the select statement I have so far: select wakeuptime,hour from ...
user avatar
1 vote
1 answer
137 views

Why can't I use ALTER to set the default on a column?

Long story short, I have a table with a TIMESTAMP column where I want to make the default CURRENT_TIMESTAMP (default currently is 1971-01-01 00:00:00). This is part of a WordPress plugin, and so ...
user avatar
-1 votes
1 answer
30 views

How to catagorize timestamp based on hours in postgreSQL [closed]

I am trying to categorize the timestamp by the HOUR using the arrival column categories like ['00.00 - 06.00', '06.00 - 10.00', '10.00 - 14.00', '14.00 - 18.00', '18.00 - 00.00']). I am very new to ...
user avatar
  • 1
1 vote
1 answer
82 views

What is the reasoning behind PostgreSQL changing the timezone when changing a timestamp with time zone field?

I have a table with a "timestamp with time zone" column. For one row, it had this value: 2021-06-24 00:00:00+02. Then, I edited and saved it to be: 2021-06-24 11:00:00-05. Note that both the ...
user avatar
3 votes
1 answer
170 views

Force NOW to return new timestamps without committing the transaction

I'm aware that, in PostgreSQL, the return value of NOW() is the transaction begin timestamp. So if you use NOW() multiple times in the same transaction it will always return the same value. And that's ...
user avatar
  • 631
0 votes
1 answer
41 views

Date calculations give different results in different environments

This query: WITH RECURSIVE cte AS ( SELECT 0 n UNION ALL SELECT n + 1 FROM cte WHERE n < 20) SELECT n, ('2020-04-13 00:00:00 Europe/Berlin'::timestamptz - make_interval(days => ...
user avatar
  • 415
1 vote
1 answer
97 views

Add a number of days to a timestamp

I'd like to add (or rather subtract) a number of days from a timestamp. For example from 2020-04-13 00:00:00 Europe/Berlin I want to subtract 20 days and I expect the result to be 2020-03-24 00:00:00 ...
user avatar
  • 415
5 votes
1 answer
402 views

How does PostgreSQL store timestamp internally?

In case of MySQL, TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC)` In case of PostgreSQL with the version less than or equal to 9.6 timestamp values ...
user avatar
  • 153
3 votes
1 answer
715 views

Is it possible to automatically generate a Unix millisecond timestamp in MySQL 5.7

I have created a created_time field with BIGINT in a MySQL 5.7 table, now I want to auto generate a Unix millisecond timestamp when I insert a record. Is it possible to do this? I have tried the code ...
user avatar
  • 477
0 votes
2 answers
3k views

MySQL - Truncated incorrect datetime value

I need to migrate some data from between to tables. The first has a varchar(255) field where a date is stored. I want to insert that date in a timestamp field in the second table. The varchar field ...
user avatar
  • 1
3 votes
1 answer
231 views

Cross join table row with generate_series

Currently I have SELECT x, plans.name FROM plans CROSS JOIN generate_series(timestamp '2021-01-01 06:00' , timestamp '2021-01-07 22:00' , interval '1 hour') ...
user avatar
1 vote
1 answer
49 views

Getting the latest timestamp in Oracle

I am trying to create a report that has the latest member id in the first column and the old member id in the second column based on the timestamp in audits table below. Member ids are stored in ...
user avatar
0 votes
3 answers
535 views

Unix timestamp vs timestamptz index performance [closed]

I'm new to the world of DB admin and have a large table (2 billion rows) of cryptocurrency tick data for multiple currencies in a Postgres database. Most of my queries will involve selecting a moving ...
user avatar
-1 votes
2 answers
157 views

How to create an index based on a time-based function in postgres? [closed]

I have a function called hot_rank to rank posts, based on their score, and their newest_comment_time (basically a decaying power function): create or replace function hot_rank( score numeric, ...
user avatar
0 votes
3 answers
273 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 &...
user avatar
  • 147
-1 votes
2 answers
315 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!
user avatar
  • 783
0 votes
1 answer
70 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 ...
user avatar
0 votes
1 answer
289 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 ...
user avatar
1 vote
1 answer
752 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 ...
user avatar
  • 111
0 votes
0 answers
30 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 ...
user avatar
  • 101
0 votes
2 answers
59 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 ...
user avatar
0 votes
1 answer
514 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. ...
user avatar
0 votes
0 answers
50 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 ...
user avatar
0 votes
1 answer
1k views

In which timezone does psql display "timestamp without timezone"?

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 ...
user avatar
  • 103
0 votes
1 answer
46 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 -...
user avatar
0 votes
1 answer
160 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 ...
user avatar
  • 1
1 vote
0 answers
54 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 ...
user avatar
3 votes
1 answer
24 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 ...
user avatar
  • 133
2 votes
1 answer
2k 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 ...
user avatar
  • 161
0 votes
0 answers
17 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 ...
user avatar
  • 143
2 votes
1 answer
3k 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 ...
user avatar
0 votes
1 answer
41 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 ...
user avatar
  • 117
1 vote
1 answer
607 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. ...
user avatar
  • 21
0 votes
3 answers
48 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 ...
user avatar
  • 255
0 votes
0 answers
102 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 ...
user avatar
0 votes
1 answer
46 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: '...
user avatar
  • 101

1
2 3 4 5 6