68

There is no difference. Three quotes from the manual: 1) These SQL-standard functions all return values based on the start time of the current transaction: ... CURRENT_TIMESTAMP ... 2) transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns. 3) now() is a traditional PostgreSQL ...


50

To go along with @ypercube's comment that CURRENT_TIMESTAMP is stored as UTC but retrieved as the current timezone, you can affect your server's timezone setting with the --default_time_zone option for retrieval. This allows your retrieval to always be in UTC. By default, the option is 'SYSTEM' which is how your system time zone is set (which may or may ...


40

Here are a couple of approaches: I've simplified your query, as you shouldn't need the TRUNC(), nor the CAST(). SELECT to_timestamp(1395036000) AT TIME ZONE 'UTC'; SELECT timestamp '1970-01-01 00:00:00' + interval '1395036000 second'; For reference, more information can be found at the following links: First query Second query


38

This is stated in a lot of places, but I think it worth mentioning always when we compare the timestamp with time zone with timestamp without time zone types: the timestamp WITH time zone does not store the time zone information along with the timestamp. What it does is to store every data in UTC time zone, as stated in the docs: For timestamp with time ...


28

From the MySQL 5.5 manual: You cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. Therefore, what you want to achieve will work in MySQL 5.5 if you add a TIMESTAMP column instead of a DATE column. The ...


26

Store timestamps as timestamp, or rather timestamptz (timestamp with time zone) since you are dealing with multiple time zones. That enforces valid data and is typically most efficient. Be sure to understand the data type, there are some misconceptions floating around: Time zone storage in PostgreSQL timestamps Ignoring timezones altogether in Rails and ...


24

Yes. There are use-cases for TIMESTAMP WITHOUT TIME ZONE. In common business apps this type would only be used for: Booking future appointments Representing the same time-of-day across various time zones, such as noon on the 23rd in Tokyo and in Paris (two different moments hours apart, same time-of-day) For tracking moments, specific points on the ...


23

SELECT extract(epoch from now() at time zone 'utc'); doesn't return the correct timestamp because postgres timezone conversion throws away timezone information from the result: 9.9.3. AT TIME ZONE Syntax: timestamp without time zone AT TIME ZONE zone Returns: timestamp with time zone Treat given time stamp without time zone as located in the specified time ...


22

On 18th of November, 1883 at 12:00 (new time), standard time was adopted by the American railroads. This means that before that time, Los Angeles used actual local time, based on mean solar time. After that, it was moved to its local time zone, which, being an integral offset of hours from the Greenwich Mean Time, was slightly different from the previous ...


18

As per the documentation, the precision of the CURRENT_TIMESTAMP is microseconds. Thus, the probability of a collision is low, but possible. Now imagine a bug which happens very rarely, and causes database errors. How hard is to debug it? It is a far worser bug than one which is at least deterministic. The more broad context: you probably want to avoid ...


16

I'd like to add another view to this, which goes against much of what's been written in the other answers. In my opinion timestamp with time zone has very little valid use cases, and timestamp without time zone should in general be preferred. First, you must understand the "UTC everywhere" pattern, which is generally recommended for all applications that ...


16

You can use generate_series for this, but be sure to explicitly cast the arguments to "timestamp without time zone" otherwise they will default to "timestamp with timezone". PostgreSQL overloads generate_series for both inputs. Problems with timestamp with timezone You can see the drawback here. SET timezone = 'America/Santiago'; SELECT generate_series(...


12

This is the default behaviour of the (first) timestamp column. Many more details can be read in the documentation: Automatic Initialization and Updating for TIMESTAMP. If you don't want this, you have to explicitly tell MySQL when you create the table. You can declare a default value (or default null) and the automatic properties will be supressed. Any of ...


11

select round( (cast(current_timestamp as date) - cast(<other_timestamp> as date)) * 24 * 60 ) as diff_minutes from <some_table>; This is what I used to calculate the difference between the current timestamp and a heart beat table entry for latency monitoring.


11

You could use the function age() to simplify your expression (returns interval). But it's much more efficient to use a sargable expression to begin with. This operates with the exact time difference (current time is relevant): SELECT * FROM accounts WHERE status = 'PENDING_PAYMENT' AND status_updated_at < now() - interval '10 days' To operate ...


10

Check this answer. Your options are: Upgrade to MySQL 5.6.5 Change the column type to TIMESTAMP, as in: ALTER TABLE `downloads` ADD `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; Create a TRIGGER THAT updates the column automatically: ALTER TABLE `downloads` ADD `date` DATETIME NULL; -- date must allow ...


10

A time zone name carries more information than an abbreviation or a simple time zone offset. 'UTC-6' is a "POSIX-style time zone specification" which is just an abbreviation plus offset. The manual on Time Zones: PostgreSQL allows you to specify time zones in three different forms: A full time zone name, for example America/New_York. [...] A ...


10

Not really because it’s possible for CURRENT_TIMESTAMP to provide two identical values for two subsequent INSERTs (or a single INSERT with multiple rows). Use a time-based UUID instead: uuid_generate_v1mc().


9

Actually, the documentation says clearly that the time zone name and abbreviation will behave differently. In short, this is the difference between abbreviations and full names: abbreviations always represent a fixed offset from UTC, whereas most of the full names imply a local daylight-savings time rule, and so have two possible UTC offsets. ...


9

Assuming your table is called TS: SELECT (EXTRACT (DAY FROM (end_ts-start_ts))*24*60*60+ EXTRACT (HOUR FROM (end_ts-start_ts))*60*60+ EXTRACT (MINUTE FROM (end_ts-start_ts))*60+ EXTRACT (SECOND FROM (end_ts-start_ts)))/60 FROM TS; Common sense, to be honest. Reduce everything to Seconds A Minute is 60 Seconds An Hour is 60 Minutes (or 60 * 60 seconds) A ...


9

You should always store data in its native data type so you can use the built-in functions. The data type of a timestamp is: timestamp. As an aside, a timestamp is not stored as a string, it's stored as an 8-byte integer, exactly the same as bigint: PostgreSQL documentation.


9

You need to apply an ORDER BY to the result. To do that, put the DELETE statement into a common table expression. Then you can apply an ORDER BY when you select from it with deleted as ( DELETE FROM @tableName WHERE id = ANY ( SELECT id FROM @tableName WHERE source = :p1 AND target = :p2 @readCondition ORDER BY ...


8

Even though you didn't post table definitions in your question so I can't tell for sure what is missed, the following indexes will be beneficial : Index on table (user_id, created_at) Index on users(id_str) or even users(id_str,screen_name,profile_image_url) I guess you already have an index on table(id) UPDATE Change WHERE : from WHERE UNIX_TIMESTAMP(t....


8

to_timestamp() expects the parameter to be given in seconds. Your value is in miliseconds. Just divide it by 1000: SELECT to_timestamp(1462975819.250); gives 2016-05-11 16:10:19.25+02


8

Why not? It allows you to see when the script was created if you save it & reload another time. The others you mention (created/last modified/last ran) aren't so useful as they're subject to frequent change. And can be grabbed from system views when/if you do need them. You can turn it off in Options here if you really want to:


7

Do not disable the triggers on your slaves, and do not configure your slaves to skip errors. When replication is set up correctly with consistent data sets and identical configurations, including tables and their data, views, triggers, stored functions, and stored procedures, it just works. If it isn't "just working," then it was already not ...


7

Here is a test script: use test drop table if exists felipe_table; create table felipe_table ( id int not null auto_increment, dt datetime not null, name varchar(25), primary key (id) ); insert into felipe_table (dt,name) values (NOW() + INTERVAL FLOOR(RAND()*10) DAY,'rolando'), (NOW() + INTERVAL FLOOR(RAND()*100) DAY,'pamela'), (NOW() + ...


7

TIMESTAMP stores the number of seconds from 1970-01-01 00:00:01 to now. It automatically converts to the date and time format when you retrieve the data. CURRENT_TIMESTAMP(): Returns the current date time with your timezone configured. UTC_TIMESTAMP(): Returns the current date and time using UTC timezone. If you're using a timezone that's not UTC, these ...


7

Add the desired INTERVAL select date + interval '5 hour' Have a look at: Date/Time Functions and Operators on Postgres documentation. select a.MyDate + interval '5 hour' as Result_Date from (select '2017-07-01 00:00:00'::date myDate) a |result_date | |:------------------| |2017-07-01 05:00:00| dbfiddle here


7

Strictly speaking: No. Because CURRENT_TIMESTAMP is a function and only one or more table columns can form a PRIMARY KEY constraint. If you mean to create a PRIMARY KEY constraint on a column with the default value CURRENT_TIMESTAMP, then the answer is: Yes, you can. Nothing keeps you from doing it, like nothing keeps you from shooting apples from your son'...


Only top voted, non community-wiki answers of a minimum length are eligible