There is no difference. Three quotes from the manual:
These SQL-standard functions all return values based on the start time
of the current transaction:
transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is
named to clearly reflect what it returns.
now() is a traditional PostgreSQL ...
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 ...
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 ...
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:
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.
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 ...
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 ...
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 ...
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 ...
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 ...
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';
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 ...
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 ...
(cast(current_timestamp as date) - cast(<other_timestamp> as date))
* 24 * 60
) as diff_minutes
This is what I used to calculate the difference between the current timestamp and a heart beat table entry for latency monitoring.
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):
WHERE status = 'PENDING_PAYMENT'
AND status_updated_at < now() - interval '10 days'
To operate ...
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
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. [...]
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().
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. ...
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
Common sense, to be honest.
Reduce everything to Seconds
A Minute is 60 Seconds
An Hour is 60 Minutes (or 60 * 60 seconds)
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.
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 (
WHERE source = :p1 AND target = :p2 @readCondition
ORDER BY ...
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)
Change WHERE :
from WHERE UNIX_TIMESTAMP(t....
MySQL is behaving correctly – your test is invalid.
If you round-trip through a time zone with DST, you will not have a lossless conversion if you hit a transition. The timestamp in question occurs during a DST transition in "CET" and "Europe/Berlin".
There are two wall clock times in Asia/Bangkok that correspond to a single wall clock time in ...
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:
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 ...
Here is a test script:
drop table if exists felipe_table;
create table felipe_table
id int not null auto_increment,
dt datetime not null,
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() + ...
The IDENTITY generator is not well documented. There are some behaviors however that can be observed that seem relevant:
The identity generation does not get affected by transactions. That means once a value has been used it will not be reused, even if the transaction causing its use is rolled back.
Not every use causes an update of the sequence position ...
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 ...