UTC time is showing incorrectly in mysql 5.1. currently, it is 9 AM  mysql&gt; SELECT UTC_TIMESTAMP, UTC_TIMESTAMP( ); +---------------------+---------------------+ | UTC_TIMESTAMP       | UTC_TIMESTAMP( )    | +---------------------+---------------------+ | 2018-08-01 14:15:03 | 2018-08-01 14:15:03 | +---------------------+---------------------+ 1 row in set (0.00 sec)   SELECT current_timestamp dt1, utc_timestamp dt2, @@global.time_zone tz1, @@session.time_zone tz2; +---------------------+---------------------+--------+--------+ | dt1                 | dt2                 | tz1    | tz2    | +---------------------+---------------------+--------+--------+ | 2018-08-01 09:15:56 | 2018-08-01 14:15:56 | SYSTEM | SYSTEM | +---------------------+---------------------+--------+--------+ 1 row in set (0.01 sec)   Please let me know how to fix it.
120935
Context: MySQL version  On MySQL 5.7.17 (as shown by SHOW VARIABLES LIKE "%version%";)  Context: MySQL timezone settings  and with SELECT @@global.time_zone, @@session.time_zone; yielding  +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM             | SYSTEM              | +--------------------+---------------------+   ...which means the server tracks the system timezone.  Context: System timezone  This being a Linux, we check the system timezone like this (output redacted):  $ timedatectl       Local time: Wed 2018-02-14 13:21:30 GMT   Universal time: Wed 2018-02-14 13:21:30 UTC         RTC time: Wed 2018-02-14 13:21:30        Time zone: Europe/London (GMT, +0000)   Context: What we know    (there is no clear description of this in , except "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)"; note that "converts TIMESTAMP values to UTC for storage" actually makes sense only as an implementor's note, it's not useful to the user - what is important is that TiMEZONE always returns the correct value in whatever timezone one is in, while DATETIME will always return the same value in whatever timezone one is in)   Context: Current time  It's now around :  $ date +"The current time is %s alias %c" The current time is 1518617572 alias Wed 14 Feb 2018 14:12:52 GMT   Problem  Let's run:  SELECT NOW(),FROM_UNIXTIME(0),UNIX_TIMESTAMP(),FROM_UNIXTIME(UNIX_TIMESTAMP());   The output (suitably pivoted by hand; there should be an option to do that):  NOW()                            2018-02-14 14:15:13  -- Display "now" in SYSTEM timetone: OK FROM_UNIXTIME(0)                 1970-01-01 01:00:00  -- Display 1970-01-01 00:00:00 in SYSTEM timetone: NOT OK UNIX_TIMESTAMP()                 1518617713           -- Correct unixtime FROM_UNIXTIME(UNIX_TIMESTAMP())  2018-02-14 14:15:13  -- Same as NOW(): OK   In the above everything is fine, except the FROM_UNIXTIME(0) giving 1970-01-01 01:00:00, which is one hour off. If Unix Time 0 were displayed in UTC, it would give 1970-01-01 00:00:00 (or would it?)  This happens if I connect locally or from a remote machine.  To make doubly sure, I checked by coercing to DATETIME. The same happens:  CREATE TEMPORARY TABLE test (N DATETIME, FUX DATETIME, UXT LONG, NN DATETIME); INSERT INTO test SELECT NOW(),FROM_UNIXTIME(0),UNIX_TIMESTAMP(),FROM_UNIXTIME(UNIX_TIMESTAMP()); SELECT * FROM test; +---------------------+---------------------+------------+---------------------+ | N                   | FUX                 | UXT        | NN                  | +---------------------+---------------------+------------+---------------------+ | 2018-02-14 14:19:51 | 1970-01-01 01:00:00 | 1518617991 | 2018-02-14 14:19:51 | +---------------------+---------------------+------------+---------------------+
65271
I want to check the time      Eastern Daylight Time   Time zone in Washington, DC, USA (GMT-4)   Thursday, May 9, 2019, 8:11 PM   However,   $ TZ="EDT" date Fri May 10 00:12:25 EDT 2019   "EST" works properly  $ TZ="EST" date Thu May  9 19:13:11 EST 2019   What's the problem?
1869
My table is:     id home datetime player resource     1 10 04/03/2009 john 399   2 11 04/03/2009 juliet 244   5 12 04/03/2009 borat 555   3 10 03/03/2009 john 300   4 11 03/03/2009 juliet 200   6 12 03/03/2009 borat 500   7 13 24/12/2008 borat 600   8 13 01/01/2009 borat 700     I need to select each distinct home holding the maximum value of datetime. Result would be:     id home datetime player resource     1 10 04/03/2009 john 399   2 11 04/03/2009 juliet 244   5 12 04/03/2009 borat 555   8 13 01/01/2009 borat 700     I have tried: -- 1 ..by the MySQL manual:   SELECT DISTINCT   home,   id,   datetime AS dt,   player,   resource FROM topten t1 WHERE datetime = (SELECT   MAX(t2.datetime) FROM topten t2 GROUP BY home) GROUP BY datetime ORDER BY datetime DESC  Doesn't work. Result-set has 130 rows although database holds 187. Result includes some duplicates of home. -- 2 ..join  SELECT   s1.id,   s1.home,   s1.datetime,   s1.player,   s1.resource FROM topten s1 JOIN (SELECT   id,   MAX(datetime) AS dt FROM topten GROUP BY id) AS s2   ON s1.id = s2.id ORDER BY datetime   Nope. Gives all the records. -- 3 ..something exotic:   With various results.
61064
Is it possible in a sqlite database to craete a table that has a timestamp column that default to DATETIME('now') ?  Like this:  CREATE TABLE test (     id INTEGER PRIMARY KEY AUTOINCREMENT,      t TIMESTAMP DEFAULT DATETIME('now') );   This gives an error... How to resolve?
5043
I currently use the following to get a local datetime from a UTC datetime:  SET @offset = DateDiff(minute, GetUTCDate(), GetDate()) SET @localDateTime = DateAdd(minute, @offset, @utcDateTime)   My problem is that if daylight savings time occurs between GetUTCDate() and @utcDateTime, the @localDateTime ends up being an hour off.  Is there an easy way to convert from utc to local time for a date that is not the current date?  I'm using SQL Server 2005
112354
Normally I would not complain about this, but given that   are based on "visiting the site" each "day" it is silly for the "day" to be based on UTC clock for all users.    I can speculate about the reason for this limitation, but I will refrain from that.  I know I am not the only one who has brought this up as an issue, but if badges are based on it, at least do the right thing and use local time.    EDIT Someone suggested that changing a user timezone creates a problem.  All times stored in the database are UTC.  If Jeff and co just do two passes for whatever logic is used for checking the badges (one for UTC - like the way it exists now, and one for local time I think that would be far more preferable.  )
56522
Ever since I started using Linux in dualboot with Windows I noticed that when I rebooted the machine, the time on the other OS was wrong, e.g. it was 12:00 on Linux, then I rebooted and on Windows it was 18:00, and when I checked the BIOS, the RTC was set to 18:00. Since my timezone is -06:00 CST I assume Linux just sets the RTC’s time to UTC and re-calculates the time based on the timezone, whereas Windows just sets the RTC to the local time. Both use the same NTP server to sync the time.  The question is, which one is doing the right thing? On which one should I set the timezone setting to UTC in order to have the right time on both?
70128
I've noticed a few posts (and it is only a few—maybe a handful) which are missing the year and time from the timestamp—The year is replaced with \yy and the time is replaced with AM/PM.  The timestamp shows as expected in the revision log (in the app) and everywhere on the site.  For example: two answers on                              App Version: 1.6.0.7 Device: iPhone 6s OS Version: Version 10.0.1 (Build 14A403)
53910
I have a form where the user can input datetime and then I want to store it in the database. Here's my schema :     'start' => array(           'type' => 'varchar', 'mysql_type' => 'datetime',           'not null' => FALSE,         ),   The form :     $form['add']['start'] = array(         '#type' => 'datetime',   '#default_value' => $date,       );   When I hit submit button, it shows me :     db_insert failed. Message = SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value:...   What I got out of it was that the DateTime in Drupal is incompatible with MySQL's. Any suggestions or how should I process the value from the form so I can insert it into the db?  Alternatively, any way to convert DrupalDatetime into string?  (For what it's worth I also tried 'timestamp' as the MySQL type but it throws me the same error).
116805
Schema:          Column        |           Type            ----------------------+--------------------------  id                   | integer                    event_id             | integer                    started_at           | timestamp with time zone   ended_at             | timestamp with time zone   created_at           | timestamp with time zone      "event_seat_state_lookup_pkey" PRIMARY KEY, btree (id)     "event_seating_lookup_created_at_idx" btree (created_at)     "event_seating_lookup_created_at_idx2" gist (created_at)   Query:  SELECT id FROM event_seating_lookup esl1 WHERE   tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)') @&gt; esl1.created_at;   Explain analyze:  Table with &lt;100k rows.   Seq Scan on event_seating_lookup esl1  (cost=0.00..1550.30 rows=148 width=4) (actual time=0.013..19.956 rows=29103 loops=1)    Filter: (tstzrange((now() - '01:00:00'::interval), (now() + '01:00:00'::interval), '[)'::text) @&gt; created_at)    Buffers: shared hit=809  Planning Time: 0.110 ms  Execution Time: 21.942 ms   Table with 1M+ rows:  Seq Scan on event_seating_lookup esl1  (cost=10000000000.00..10000042152.75 rows=5832 width=4) (actual time=0.009..621.895 rows=1166413 loops=1)   Filter: (tstzrange((now() - '01:00:00'::interval), (now() + '01:00:00'::interval), '[)'::text) @&gt; created_at)   Buffers: shared hit=12995 Planning Time: 0.092 ms Execution Time: 697.927 ms   I have tried:  VACUUM FULL event_seating_lookup; VACUUM event_seating_lookup; VACUUM ANALYZE event_seating_lookup; SET enable_seqscan = OFF;   Problem:  event_seating_lookup_created_at_idx or event_seating_lookup_created_at_idx2 indexes are not being used.  Notes:   PostgreSQL 11.1. btree_gist extension is installed. I have tried equivalent setup with created_at timestamp without time zone and using tsrange; same result. I understand that rewriting the query with &gt;=, &lt; checks would make it use the btree index. The question is what is the reason the index is not used with the tstzrange containment operator and if there is a way to make it work.
18946
Here is my ticks (timeclock) table:  _id_ #employee_id   created_at   This table stores, for each employee, the timestamp they tick. Sometimes some of them tick multiple times a day, some only once or none at all when they didn't come to work.  I have a query which allows me to know exactly how many times an employee wasn't at work during the last 7 days, but I would like to have in the results the days they didn't work. Which means, every employee who worked less than 6 days in the last 7 days, and which dates are missing. And I have no clue.  Here is how I query who was absent more than once during the last 7 days, and how many times:  SELECT `id`, `employee_id`, COUNT(`date`)-6 missing FROM (     SELECT `id`, `employee_id`, DATE(created_at) date     FROM `ticks`     WHERE `created_at` &gt;= '2017-09-16' AND `created_at` &lt; '2017-09-23'     GROUP BY `employee_id`, `date` ) ticks GROUP BY `employee_id` HAVING missing &lt; 0   Based on that or another query, I would like to know which date is missing in that range (by the way, I can get the range from another query, there is always at least 1 tick per day, even on Sunday).  Here is an  with an example dataset. The results I'm expecting, would be:  +-------------+--------------+ | employee_id | date_missing | +-------------+--------------+ |           2 |   2017-09-18 | |           2 |   2017-09-19 | |           3 |   2017-09-20 | +-------------+--------------+
98597
In PostgreSQL 9.5, I have tables with columns in the form   prefix_last_modified timestamp without time zone NOT NULL DEFAULT  (clock_timestamp() AT TIME ZONE 'UTC')   I was looking for a way to set the last modified value automatically updated at each update of the rows, and I found  that defined the function:  CREATE OR REPLACE FUNCTION update_modified_column()  RETURNS TRIGGER AS $$ BEGIN     NEW.modified = now();     RETURN NEW;  END; $$ language 'plpgsql';   Now, I'd like to know if there is any way to pass the column name to the PostgreSQL function and to execute it to the NEW row? E.g.  CREATE OR REPLACE FUNCTION update_modified_column(varchar column)        RETURNS TRIGGER AS $$     BEGIN         NEW.column = now();         RETURN NEW;      END; $$ language 'plpgsql';
79477
'timedatectl' is giving following output  -                        Local time: Wed 2018-06-13 18:08:51 IST                   Universal time: Wed 2018-06-13 12:38:51 UTC                         RTC time: Wed 2018-06-13 12:38:51                        Time zone: Asia/Kolkata (IST, +0530)        System clock synchronized: no systemd-timesyncd.service active: yes                  RTC in local TZ: no   How to set System clock synchronized to yes?
66914
All the times seem to be in GMT. Is there a time zone setting so they will show up in my time zone?  Also will this affect the "consecutive day" badge calculations?
103971
CREATE TABLE plan    (       id                VARCHAR(30) NOT NULL,       name              VARCHAR(20),       amount            INT,       interval          INT,       currency          CHAR(3),       object            VARCHAR(20),       livemode          BOOLEAN,       interval_count    INT,       trial_period_days INT,       created           TIMESTAMP,       updated           TIMESTAMP,       deleted           TIMESTAMP,       PRIMARY KEY (id)    );    Produces   'ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval          INT,       currency          CHAR(3),       object            ' at line 6'   Doesn't work. However if the 'l' at the end of 'interval' is removed. It works.  CREATE TABLE plan    (       id                VARCHAR(30) NOT NULL,       name              VARCHAR(20),       amount            INT,       interva           INT,       currency          CHAR(3),       object            VARCHAR(20),       livemode          BOOLEAN,       interval_count    INT,       trial_period_days INT,       created           TIMESTAMP,       updated           TIMESTAMP,       deleted           TIMESTAMP,       PRIMARY KEY (id)    );    I don't understand if this is a problem with attempting to use 'interval' and 'interval_count'. What's going on here.
30416
this code always returns 0 in PHP 5.2.5 for microseconds:  &lt;?php $dt = new DateTime(); echo $dt-&gt;format("Y-m-d\TH:i:s.u") . "\n"; ?&gt;   Output:  [root@www1 ~]$ php date_test.php 2008-10-03T20:31:26.000000 [root@www1 ~]$ php date_test.php 2008-10-03T20:31:27.000000 [root@www1 ~]$ php date_test.php 2008-10-03T20:31:27.000000 [root@www1 ~]$ php date_test.php 2008-10-03T20:31:28.000000   Any ideas?
65366
I have a table of events with a recorded start and end time as a MySQL DATETIME object (in the format YYYY-MM-DD HH:MM:SS. I want to find all events that occur in a specific date range. However, events can span multiple days (and go outside of my date range, but I want to return them if they even overlap by 1 second or more with my date range).  Suggestions?