For questions about timestamp data types and/or recording a particular point in time.
Questions tagged [timestamp]
316 questions
137
votes
2 answers
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 don't get the expected time from PostgreSQL:
This…
Jonas
- 33,945
- 27
- 62
- 64
123
votes
2 answers
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 …
JohnMerlino
- 1,939
- 5
- 20
- 21
75
votes
7 answers
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 PostgreSQL.
What I would like to know is: Are…
Marcus Junius Brutus
- 3,409
- 7
- 30
- 44
59
votes
3 answers
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;
…
Jonas
- 33,945
- 27
- 62
- 64
50
votes
6 answers
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();
+---------------------+
| UTC_TIMESTAMP() |
+---------------------+
| 2012-07-01…
Adam Matan
- 12,079
- 30
- 82
- 96
39
votes
2 answers
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:
timestamp NOT NULL DEFAULT (now() AT TIME ZONE…
Bam
- 579
- 1
- 5
- 10
25
votes
2 answers
Can't default date to CURRENT_TIMESTAMP in MySQL 5.5
I am not able to set Current_timestamp as default value. My Mysql version is 5.5.47.
Query is
ALTER TABLE `downloads` ADD `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ;
While it is working fine on my local DB with mysql V5.6.56.
urfusion
- 353
- 1
- 4
- 9
21
votes
1 answer
How to convert a unix time to PostgreSQL' Timstamp without timezome?
I have a PostgreSQL database running on a server which has its time zone set to India's Time Zone (i.e. UTC +5:30)
I have some data in a table which is created like this:
CREATE TABLE "CLOUDDATA"
(
"CD_Tm_Obs" timestamp without time zone,
…
Devdatta Tengshe
- 631
- 4
- 9
- 16
19
votes
3 answers
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?
John Puskin
- 377
- 2
- 3
- 8
18
votes
1 answer
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 Argument Type …
Evan Carroll
- 65,432
- 50
- 254
- 507
16
votes
1 answer
Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres?
In Postgres 9.5, I was surprised to see the result seen below while experimenting with year 0001 (no year zero 0000).
Offset of -07:52:58?
Some example code. Note that I mixed use of TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE, so read…
Basil Bourque
- 11,188
- 20
- 63
- 96
14
votes
1 answer
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 modified_timestamp >…
bsiamionau
- 243
- 1
- 2
- 6
13
votes
3 answers
"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
---------------+--------+------------+--------
Europe/Berlin | CET…
sufleR
- 678
- 7
- 18
12
votes
1 answer
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 need a different "datestyle" setting.
CONTEXT: …
falcs
- 223
- 1
- 2
- 4
12
votes
3 answers
Storing time with milliseconds in database
Just a quick question regarding storing "time" in a database. I'm logging the time of users runs which are in the format 00:00:00:00 (hours, minutes, seconds, milliseconds).
I was originally going to store it as TIME but then I realised MySQL…
Elliott
- 297
- 2
- 4
- 8