Questions tagged [timestamp]
For questions about timestamp data types and/or recording a particular point in time.
29 questions
18
votes
1
answer
46k
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 ...
- 65.9k
124
votes
2
answers
133k
views
Difference between now() and current_timestamp
In PostgreSQL, I use the now() and current_timestamp function and I see no difference:
# SELECT now(), current_timestamp;
now | now
------------...
- 1,949
11
votes
1
answer
7k
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'::...
- 245
13
votes
3
answers
7k
views
"AT TIME ZONE" with zone name PostgreSQL bug?
I was answering this stackoverflow question and found strange result:
select * from pg_timezone_names where name = 'Europe/Berlin' ;
name | abbrev | utc_offset | is_dst
---------------+--...
- 678
5
votes
1
answer
2k
views
Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?
Setting time zone as numeric offset (without abbrev, thus IMO not in POSIX format) is still interpreted by PostgreSQL as POSIX format:
SET TIME ZONE '+02:00';
At least I assume, because checking the ...
- 297
138
votes
2
answers
292k
views
How do I get the current unix timestamp from PostgreSQL?
Unix timestamp is the number of seconds since midnight UTC January 1, 1970.
How do I get the correct unix timestamp from PostgreSQL?
When comparing to currenttimestamp.com and timestamp.1e5b.de I ...
- 34.1k
75
votes
7
answers
140k
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 ...
- 3,419
39
votes
2
answers
71k
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 options:...
- 579
14
votes
1
answer
19k
views
Is there way to get transaction commit timestamp in Postgres?
I have data-pulling functionality that once in 5 seconds grabs all the data from Postgres table basing on modified_timestamp column. It works the following way:
SELECT * FROM my_table WHERE ...
- 243
4
votes
2
answers
484
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 ...
- 235
1
vote
2
answers
2k
views
Tables accessed during last period
I want to check which tables have been updated during a given period, for instance in descending order of access time per table.
How can I get that for PostgreSQL?
- 167
50
votes
6
answers
174k
views
MySQL Set UTC time as default timestamp
How do I set a timestamp column whose default value is the current UTC time?
MySQL uses UTC_TIMESTAMP() function for UTC timestamp:
mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| ...
- 12.2k
19
votes
3
answers
16k
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?
- 387
7
votes
1
answer
22k
views
Why does mysql adds a default timestamp on insert/update even if I did not specify anything?
I have a table that is as follows:
CREATE TABLE SomeTable (
id int unsigned NOT NULL AUTO_INCREMENT,
status enum('broken','repaired') NOT NULL,
from datetime NOT NULL,
until ...
- 791
6
votes
1
answer
16k
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 ...
- 607