53

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 ...


45

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 ...


31

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


23

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 ...


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 ...


21

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 ...


18

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 ...


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 ...


12

the result of the expression to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND') is a value of the oracle datatype DATE. This type contains date and time. If you want to display this value to a user you must convert it to a string. This conversion can be done by an application or you can let oracle convert it to a string type as it ...


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 ...


10

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.


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

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 ...


9

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(...


8

This should never fail (I simplified a bit): WHERE status_code IN ('30000','30005') AND expiration > now() PostgreSQL can compare date and timestamp (with or without time zone) automatically. If one is a date it is cast to timestamp automatically (0:0 hours). The error message tells a different story. You are actually trying to input a date with ...


8

An alternative is to store each part of the date in a numeric field. So you would have three fields: year SMALLINT # Store positive values for AD and negative for BC years. month TINYINT day TINYINT This way it would still be human readable. The range of values for different numeric data types in MySQL are available at Overview of Numeric Types. The ...


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

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. ...


8

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 ...


8

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 ...


8

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


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

No native RDBMS date data type is going to do for applications that require very old (and for some, even distant future) dates. If I were you, I'd use a string type for the native storage and stick with a place-significant format like: +YYYY-MM-DD to accomodate BC/AD and any foreseeable historical or reasonable future date. If it might help, you could ...


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

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 consistent ...


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'...


6

Personally, I'd use integer types for duration Example: 340,000 milliseconds is 340000 in an unsigned int column. To me, datetime, time, date etc are for explicit points in time. 340 seconds is meaningless in that context. Having a start date/time allows the duration to be added of course


6

I have used CHAR and VARCHAR in the past, replacing the missing pieces with question marks or dashes. Question marks mean "not known", and dashes meant "not applicable". This proved to be intuitive enough for the users (secretaries and paralegals in complex litigation), flexible enough for the lawyers, and it sorted sensibly. "1964------" "1964-??-??" "1964-...


6

And that's still not the gist of it! I ran into a very similar problem some time ago. The major cons of time zone abbreviations have been presented here already: they do not take DST (daylight saving time) into account. The major pro: simplicity resulting in superior performance. Taking DST rules into account makes time zone names slow in comparison. Time ...


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